Press "Enter" to skip to content

Category: Power BI

Power BI Data Models with Multiple Aggregation Tables

Published 2021-09-21 by Kevin Feasel

Phil Seamark continues a series on Power BI aggregations:

Why might you consider having more than one aggregation table? The short answer is speed and overall resource efficiency.

Consider a heavily used Power BI report where page-load time is considered critical. A typical report may have half a dozen visuals on a page showing values computed over various grains. If the model used by the report has no aggregation tables, all calculations use the raw fact tables to produce values for each metric.

Adding an aggregation table to the model allows the same calculations as before to use smaller tables to produce the same result. Calculations using smaller aggregation tables will enable the server hosting the data model to use much less effort per query.

It was interesting to see just how easy the process is.

Comments closed

Remember CPU Time when Optimizing Power BI Premium Reports

Published 2021-09-20 by Kevin Feasel

Chris Webb has a public service announcement:

When you tune a Power BI report or dataset refresh your first job is to make it run faster – users hate slow reports and late data. However, if you are using Power BI Premium you also need to think about reducing the amount of CPU used by a query or refresh as well, and in this post I’ll explain why.

Click through for that explanation.

Comments closed

Connecting to Power BI PPU via SQL Server Profiler

Published 2021-09-15 by Kevin Feasel

Gilbert Quevauvilliers wants to use SQL Server Profiler for a good reason:

I was recently looking on how to use SQL Profiler to connect to my Premium Per User (PPU) dataset so that I could see where the processing of my incremental partitions was.

When I first tried to connect using the default options, I got the error “Either the race with the ID of ‘AAA’ does not exist in the server of ID ‘BBB’, or the user does not have permissions to access the object”

This was rather a confusing error and it took me a little while to figure it out.

Click through to see the solution.

Comments closed

Measure Filters in Power BI

Published 2021-09-14 by Kevin Feasel

Marco Russo and Alberto Ferrari dive into a topic:

The first paragraph of this article needs to be a warning: the article itself is here for DAX and Power BI enthusiasts only. We are going to show a report that does not work, and then we explore how to fix the problem by performing a deep analysis of the queries generated by Power BI, finding the problem, and finally fixing it. The article contains a lot of references to advanced DAX concepts and the final solution is NOT a best practice. The value of the article is not in the specific solution. Rather, the important part is that a deep understanding of DAX and Power BI can help you obtain the right results, specifically when you have the feeling that you are faced with a bug because Power BI is acting strange. If you do not like DAX before reading this article, you will like it even less at the end. But if you love DAX, then chances are you will really enjoy the reading, even though it requires quite a lot of brain bandwidth. For sure, it took all of mine when I first encountered this behavior.

Break out the propeller hats before you dive in.

Comments closed

Leading Zero Padding with DAX and M

Published 2021-09-10 by Kevin Feasel

Soheil Bakshi gives us two methods to pad those numbers:

There are some cases that we want to add a leading zero to a digit, such as showing 01 instead of 1, 02 instead of 2 and so on. We have two options to do this in Power BI, doing it in Power Query or doing it with DAX.

Click through for the two methods, and for a bonus item which incidentally bolsters the argument that dates should be YYYY-MM-DD.

Comments closed

Tying DAX Queries to Visuals in a Power BI Report

Published 2021-09-08 by Kevin Feasel

Chris Webb shows off some diagnostic skills in published Power BI visuals:

The integration between Azure Log Analytics and Power BI opens up a lot of new possibilities for Power BI administrators. It’s still in preview (there’s a lot more cool stuff still to come) but the Analysis Services events that are available at the time of writing are still very useful: they give you pretty much everything you had in Profiler plus some new stuff too. In this post I’ll show you how you can use this data to work out which visual in a published report generated a particular DAX query.

Read on for a demonstration.

Comments closed

Dynamic Transparency Changes in Power BI

Published 2021-09-08 by Kevin Feasel

Sandeep Pawar explains how to allow users to control transparency in Power BI visuals:

As someone who uses Python/R heavily for exploratory data analysis and Power BI for publishing the final data analytics reports, I have always missed the ability to adjust the color transperancy in Power BI. In Power BI you can change the color dynamically and conditionally but there is no native functionality to change the transperancy.

I was working on a project where I wanted to highlight certain clusters in the data to the business user. Sure, I could change the color but it’s very challenging when the data points are concentrated in a small area and they overlap each other. In Python and R you can easily adjust the alpha value in most plots to see the dense area clearly.

Click through for one Power BI solution.

Comments closed

Relative-Time Slicers in Power BI

Published 2021-09-07 by Kevin Feasel

Gerhard Brueckl wants your Power BI reports to know what day it is:

A very common requirement for a Power BI report that I stumble across at almost all of my customers is to automatically show data for the current day/month/year when a report is opened. At first sight this seems like a very trivial problem but once you dig into the problem, you will realize that all of the common solutions out there have some disadvantages and only solve the problem partially.

Click through for a clear description of the problem and a clear description of the answer.

Comments closed

An Introduction to Aggregations in Power BI

Published 2021-09-07 by Kevin Feasel

Phil Seamark begins a new series:

One of the most powerful features of Power BI data modelling today is creating aggregation tables in your dataset and having simple calculations automatically make use of the tables without writing complex DAX. This feature is available in both Pro and Premium.

What does “automatically make use of” mean in this context?

Read on for that answer and a whole lot more.

Comments closed