Press "Enter" to skip to content

Category: Power BI

Using a Date Template in Power BI

Published 2021-05-05 by Kevin Feasel

Haroon Ashraf recommends using a template with date dimension details:

A Power BI Template

A Power BI template is a structure or model that typically contains commonly used tables, relationships, and hierarchies belonging to an organization or an individual. This model is reused in any Power BI report. More information is provided in the previous article:

Centralized Data Modelling using Power BI Templates

What is a Date Template in Power BI?

A Date template is a precise structure of the Date table that is a background for building reports in the organization. In other words, it is like a built-in Date table that any reports developer or a skilled business user can apply to build Power BI reports.

Read on for more Q&A as well as how to create a simple version of a date table for this template. The idea of using a template makes even more sense as you have more complicated date table requirements, such as adding in fiscal year details, holiday information (especially holidays which don’t always fall on the same solar calendar day, such as Passover or Easter), and dates important to the company.

Comments closed

Measuring DirectQuery Performance

Published 2021-05-03 by Kevin Feasel

Chris Webb shows how you can use the Performance analyzer in Power BI to measure DirectQuery performance:

If you have a slow DirectQuery report in Power BI one of the first questions you need to ask is how long the SQL queries that Power BI generates take to run. This is a more complicated question to answer than you might think, though, and in this post I’ll explain why.

I happen to have access to some of the famous New York taxi data in a Snowflake database, and in there is a table with trip data that has 173 million rows that I have a built a Power BI dataset from. The data and the database used are not really important here though – what is important is that it’s DirectQuery and a large-ish amount of data.

Read on for more information on how it all works.

Comments closed

Turning On and Off Calculations in Power BI Visuals

Published 2021-05-03 by Kevin Feasel

Phil Seamark has a clever workaround:

Power BI doesn’t yet have a feature that allows end-users to turn on/off the ability to process calculations for visuals on a report page until they are ready. Most of the time, this is perfectly fine – however, in some instances, it can be handy to disable long-running and heavy calculations from running. At the same time filters/slicers are get selected.

The scenario you most likely want to have this control is when your model uses Direct Query mode against large tables in data sources that charge you for query processing. Even if your Direct Query data source does not charge per query, having a user make quick-fire selections over several slicers can potentially saturate a back-end data-source and unnecessarily chew up resources.

Read on to understand how to use calculation groups to do this, as well as the limitations around this solution.

Comments closed

Just Take My Money: Paying for Power BI Premium per User

Published 2021-04-30 by Kevin Feasel

Wolfgang Strasser shows how hard it can be to let someone take your money in return for goods or services:

Initially I told my customers, purchase the PPU license in the Microsoft 365 portal and thought that it should be an easy deal.. but hey – it’s licensing, Microsoft licensing involved…

The place where to buy the PPU add-on is not that obvious as it looks like.

Read on to learn how to upgrade to Premium per User if you already have a Professional license.

Comments closed

Data-Driven Subscriptions in Power BI

Published 2021-04-29 by Kevin Feasel

Patrick LeBlanc shows us how to build data-driven subscriptions using Power BI and Power Automate:

Automate data driven subscriptions with the Power Platform using Power BI and Power Automate! Patrick shows you how to quickly setup a report bursting option for your reports.

Click through for the video. There are a few more steps compared to what you’d do in Power BI Reporting Services, but it’s still pretty straightforward.

2 Comments

The Power Automate Custom Visual in Power BI

Published 2021-04-27 by Kevin Feasel

Imran Burki tries out a new custom visual:

Using the Power Automate Custom Visual in Power BI is the same process as using any custom visual. We’ll use the Defects Dashboard I created from my last blog post as an example. We want to send a Teams message when we notice defects in a plant require the attention of the plant supervisor. After that, we want to create a meeting in Outlook to discuss findings from our dashboard. Previously, there wasn’t a straightforward way to do this directly in Power BI. However, with the Power Automate Custom Visual, we can create flows directly in Power BI without ever having to leave Power BI! Now that’s cool! Let’s get started.

This is really interesting for setting up rules-based alerting.

Comments closed

DirectQuery on REST APIs

Published 2021-04-27 by Kevin Feasel

Chris Webb illuminates us:

One of the most common questions I get asked is “How can I use Power BI in DirectQuery mode on top of a REST API?”. This seems like a reasonable thing to do but almost everyone who tries it will fail, and in this post I will explain why.

To answer this question we first of all have to review the two main ways of working with data in Power BI: Import mode and DirectQuery mode. In Import mode data is cached in Power BI’s own internal database and all the DAX queries that are generated by your reports are answered from there. In DirectQuery mode no data is stored inside Power BI; instead, when a report is run and DAX queries are fired off against your dataset, Power BI in turn generates queries against the data source to get the data needed. Most of the data sources that can be used with DirectQuery mode in Power BI are relational databases and so that means Power BI will generate SQL queries to get data from them, but Power BI can also generate queries in other languages too.

Read on for the bad news, although there are some third-party products which can make it work in specific cases.

Comments closed

Pareto Charts in Power BI

Published 2021-04-22 by Kevin Feasel

Imran Burki builds a chart:

Last week we built a Manufacturing Yield dashboard that showed first and final pass yield numbers. In this post, we’re going to introduce the concept of manufacturing defects and build a Pareto chart in Power BI. Unlike Excel, the Pareto chart isn’t out-of-the-box in Power BI. Instead, we must create DAX to build the Pareto. Before we dig into the DAX, let’s talk about why we would create a Pareto in the context of manufacturing and why defects are important to track.

Read on to learn what a Pareto chart is and how you can build the DAX function which gives us the relevant information.

Comments closed

From Azure Analysis Services to Power BI PPU

Published 2021-04-21 by Kevin Feasel

Gilbert Quevauvilliers teases a new series:

I have been doing a lot of evaluation and investigations for organizations who currently are using Azure Analysis Services (AAS) and looking to see if they can leverage Power BI Premium Per User (PPU)

In this series I am going to cover the following details below, which I completed to see if the migration was not only feasible but should be the new normal.

Looks like it will be an 11-parter, so we have some reading to look forward to.

Comments closed