Press "Enter" to skip to content

Category: Power BI

Asymmetric Crosstabs in Power BI

Published 2019-08-27 by Kevin Feasel

Teo Lachev shows how we can implement asymmetric crosstabs in Power BI:

The Internet column shows the sales amount from FactInternetSales. Then, the matrix pivots on the BusinessType column in the FactResellerSales. Because, Internet sales don’t relate to BusinessType, it doesn’t make sense to pivot it. Instead, we want to show Internet sales in a single static column before the crosstab portion starts.

Implementing such a report in SSRS is easy thanks to its support of adjacent groups and static columns but not so much in Power BI. The issue is that Matrix would happily pivot both measures and the InternetSalesAmount would be repeated for each business type.

The solution isn’t awful, but it does involve knowledge of DAX.

Comments closed

Power BI Practices: Good and Best

Published 2019-08-27 by Kevin Feasel

Paul Turley has a great document plus checklist on Power BI practices:

I find there there are so many things to remember when starting a project that a checklist is handy. I’ve been collecting the following as notes for some time. Bare with me as I work on consolidating this article into a concise checklist.

The purpose of this article is to outline a set of guidelines and recommended practices for managing Microsoft Power BI projects. This guide is primarily focused on the work performed by the IT-managed BI Solution Developers for formally-managed BI projects.

If you work heavily with Power BI, you’ll really want to read this and review Paul’s checklist at the end.

Comments closed

Storytelling with Power BI: Consistency

Published 2019-08-23 by Kevin Feasel

Mark Lelijveld continues a series on storytelling with Power BI:

In the below report you can easily click on a country on the left side to move to another page. When it comes to interactivity it is all done right! On the right top you can also filter on order date. Let’s say we apply a filter to only see the sales up to the end of 2013. This results in a sales amount of nearly $ 319K.

Now, Germany gets my attention. I want to see more and decide to navigate to the other page by clicking on Germany. Ending up at the other page, I see that the sales amount changes back to $2.3M. In other words, my filter is gone!

Much of the difference between adequacy and excellence with visualization is in this kind of polish.

Comments closed

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