Press "Enter" to skip to content

Category: Power BI

Blank Rows and DAX

Published 2019-08-21 by Kevin Feasel

Alberto Ferrari explains how different DAX functions treat blank rows differently:

DAX offers two functions to retrieve the list of values of a column: VALUES and DISTINCT. The difference between the two is subtle. To understand it better, we first need to introduce the concept of the blank row. The blank row is a special row added to a table, in case the table is on the one-side of a strong relationship and the relationship is invalid. Let us elaborate on this.

Click through for the detailed explanation, along with plenty of examples.

Comments closed

Power Query and the Benefits of Immutability

Published 2019-08-19 by Kevin Feasel

Chris Webb explains why immutable expressions can be faster to run multiple times than mutable processes:

Instead of taking the value #”Sorted Rows”[Column2]{0} and storing it in the variable Column2 then adding Column2 four times, I’m  adding the expression #”Sorted Rows”[Column2]{0} together four times. The query returns the same number as the previous query. However this query takes 20 seconds to run! Why?

Read on for the explanation.

Comments closed

The Importance of Interaction in Power BI

Published 2019-08-16 by Kevin Feasel

Marc Lelijveld continues a series on storytelling with Power BI:

Many times, I see reports with loads of visuals on the pages. This results in both a really poor performance, as well as the end user has no clue what the key message is of this report. You can always ask yourself, is this visual necessary to show on this page? What does it add to this page? Is this really needed? If not, remove it! If the visual does add some value, is it needed on this page? Maybe it is only distracting the user of where the report is about.
A good approach can be to put certain visuals on a different page or hide them by default until the user interacts with the report. Within the interaction, you will have multiple options in Power BI to interact with your user.

There’s a lot more to it, so read on.

Comments closed

Running Power BI Desktop as a Different Account

Published 2019-08-15 by Kevin Feasel

Gilbert Quevauvilliers shows how you can run Power BI Desktop as a different domain account, even when your logged-in account is not on the domain:

This is not the greatest error message.

Fortunately, I knew that I could connect to the server so the issue was not with connectivity but more around how could I authenticate against Analysis Services.

NOTE: Analysis Services can only Authenticate against domain accounts, and that is why I got the error above.

Click through for the solution.

Comments closed

Power BI Icon Names

Published 2019-08-15 by Kevin Feasel

Matt Allington is on a quest to find all of the Power BI icons:

The benefit of this approach is you can leverage all the standard icons and then add you own icons as well, effectively extending the default icon set. But where can I find a list of all the names of the standard icons?

I asked this question in the Power BI Blog thread (as did some others). A list of names was provided by Francisco Mullor in the comment section of that blog post (sorry, I can’t seem to link to the exact comment). I have taken the information provided by Francisco and produced the following Power BI report.

Matt also fixes a display issue that you might run into when using these.

Comments closed

Optimizing Max Value Performance in Power Query

Published 2019-08-12 by Kevin Feasel

Chris Webb shows us how to speed up a query to get the maximum value in a column:

In part 1 of this series – which I strongly recommend you read before reading this post – I showed how removing columns from a table can make a dramatic improvement to the performance of certain transformations in Power Query. In this post I’ll show some tricks taught to me by Curt Hagenlocher of the dev team that can improve performance even more.

Click through for the trick and an explanation of when it works and when it doesn’t.

Comments closed

Guiding Your Users with Power BI

Published 2019-08-09 by Kevin Feasel

Marc Lelijveld continues a series on storytelling with Power BI:

Another thing to think about before building visuals, is the click path. In order to make sure we’re really telling the story, we need to get our click path in a logical order. This is most important for both, declarative and exploratory storytelling.

Maybe you want to provide all the information in your visuals right away. Sometimes I see things like this happening when people add a lot of visuals on only one page. In my experience it is better to make your report interactive and work with multiple pages. Spread the story you want to tell out over multiple pages. This is what Will Thompson also has done as well in this report for the Gartner Bakeoff in 2018.

This sort of planning helps you out in the long run.

Comments closed

Getting Max Column Value from Power Query

Published 2019-08-09 by Kevin Feasel

Chris Webb hits us with techniques to get the maximum value in a column using Power Query and M:

In this first part I’m going to set up the scenario and show you what I found out from my own experimentation. The really mind-blowing tricks shown to me by the ever-helpful Curt Hagenlocher of the Power Query dev team will be covered in part 2.

Let’s say you have a large csv file which contains a numeric column and you want to get the maximum value from that column. In this case I’m going to use the 2018 Price Paid data from the UK Land Registry available here. This csv file contains 1021215 rows, one for each property transaction in England and Wales in 2018; the second column in this file contains the the price paid for the property, so the aim here is to get the maximum price paid for all property transactions in 2018.

Read the whole thing.

Comments closed

Version Control and Power BI Desktop

Published 2019-08-07 by Kevin Feasel

Gilbert Quevauvilliers takes us through version control with PBIX files:

In the second part of my blog post I am going to detail how to use the version control with Power BI Desktop files.

This will include adding files, checking files in and out, viewing previous versions and reverting to previous versions.

If this is the first time you are reading this blog post, I would highly suggest reading Setting up Version Control for my Power BI Desktop Files (PBIX) with no additional Cost * | Part 1

In short, Gilbert treats PBIX file as any other data file. These can get kind of beefy, though, so I’ve also saved them as templates—that way, you get the structure without pulling in all of the data.

Comments closed