Category: Power BI

Jon Fletcher shows us how to sort a Power BI table by more than one column:

A common request that is raised by clients is how to sort a table in Power BI by multiple columns, in the same way you can in Excel.
For a while, there was no way (at least no easy way) to do this until the Power BI March 2020 update.

I learnt this tip from the following YouTube video:
https://www.youtube.com/watch?v=ik0K1H9j2Uc
Full credit to Dhruvin Shah, check his video out.

I would call this feature moderately discoverable—once you see how to do it, you can say “Oh, that makes sense.” But it’s not something I would necessarily have thought to do without this prompting.

Comments closed

Alice Drummond has two articles on improving the map experience in Power BI. First up is creating drill-down maps:

But why would we want to drill down on a map?

See the big picture: Using drill down, we can visualise the dataset at the ‘big picture’ scale, and then explore finer details for areas of interest (just think of the classic spatial hierarchy of Country à State à City à Suburb)

Save on real estate: We can also display multiple datasets at different levels in the same map – which goes a long way to conserving your precious report page real estate by reducing the number of visuals in your report!  

Performance: Following on from above, less visuals almost always equates to better report performance! And this is especially true when using MapBox, which is at the slower end of the Power BI performance scale at the best of times – so using drill down to reduce the number of maps combined with filtering your datasets goes a long way to speeding up your reports!

The second post is all about styling choropleth maps:

If you’ve played around with MapBox in Power BI – you’ll know that it has loads of great features to create really rich and beautiful maps, including some great ‘out of the box’ map styles (i.e. base maps). However, you might not be aware that it also gives you the ability to design custom styles with your own spatial layers. I discovered this feature on a recent project where my client wanted to include Victoria’s Catchment Management Authority (or CMA) boundaries on their base-map to provide greater context to help interpret their data. Up until this point, the only option I knew of was to purchase an expensive ArcGIS Online licence to create custom map styles. So, you can imagine just how excited I was when I discovered that I could also do this in MapBox – for FREE!!!

Click through for the posts and videos.

Comments closed

Marc Lelijveld shows how you can create custom tooltips per user in Power BI:

From an end user perspective, it is very user friendly to see that the report is adjusted to your personal preferences. Similar is the case for page navigation. If I am the country manager for Germany, I would prefer the report to be adjusted to my area.

With row level security you can filter the report down to only the specified area you want. But in this case, we want clarity over all countries. So, we do not want to filter the dataset itself, but only customize the end user interaction. For this setup, we will still use row level security, but in a different setup.

Click through to see how to do this.

Comments closed

Melissa Coates has an updated whitepaper for us:

I’m really excited to announce that a new version of the Microsoft whitepaper “Planning a Power BI Enterprise Deployment” is now available.

This is version 3 of the whitepaper that I co-authored with Chris Webb. The previous version was from July 2018, so this update includes quite a lot of changes throughout.

Huge high-five to Meagan Longoria who was our tech reviewer again. She never fails to make my writing better.

That’s three very sharp people, so you can bet it’s going to be good.

Comments closed

Marco Russo goes over the ramifications of a recent change to Power BI Desktop:

Starting from the May 2020 version of Power BI Desktop, regardless of the Windows locale settings DAX always uses standard separators by default. This change does not affect most Power BI users around the world; but if you use the comma as a decimal separator and 10,000 is just ten and not ten thousand, then you are affected by this change.

First of all, you can restore the previous behavior as I describe in this post, but the recommendation and the default are now to use the standard DAX separators. I want to describe why this (I think good) choice was made and how I contributed to making this happen.

Read the whole thing.

Comments closed

Dustin Ryan shows how you can have Power BI perform incremental refresh against a .NET Web API source:

The customer is using Power BI to report on data from Service Now via APIs. So the customer was able to quickly connect Power BI to Service Now data and begin reporting on relevant datasets very quickly. The challenge, however, is that querying multiple years of data via the API was less than desirable for a variety of reasons.

The customer was interested in exploring the incremental refresh capabilities of Power BI, but were worried about using Power BI’s native incremental refresh capability since query folding (if you’re new to query folding, read this here) is not supported by Power BI’s web connector. So the customer team reached out to me with the challenge and I worked up an example that I think will help them meet their requirement.

Click through for the solution.

Comments closed

Ed Hansberry gives us a second option for filtering dimension values:

I don’t like loading up a slicer with dozens or hundreds of items that have no corresponding records. The same would apply if there was no slicer, but the consumer wanted to filter using the Filter pane. So I’ll filter the customer table so it only includes what I would call “active customers” that are shown in the sales table.

The most straight forward way to do this is by doing an Inner Join between the tables, but there is another way, using the powerful List.Contains() feature of Power Query. And what makes it so powerful is not just it’s utility, but when you run it against data in a SQL Server or similar server, Power Query will fold the statement.

Let me walk you through both methods so it is clear.

Read on for the walkthrough.

Comments closed

Gilbert Quevauvilliers fixes a problem of overlap:

Recently a user sent me a message saying that they could no longer click on a slicer item. I tried to myself and I had no issues clicking on the slicer item.

I then had an online screen share with the user, so that I could see what was happening when they were trying to click on the Slicer item. What had happened is they were using a lower screen resolution. What this did then was to place the Visual Header Icons over multiple lines, which then did not allow the top slicer item to be clicked on.

Click through for the solution.

Comments closed

James Serra gives us a few options to monitor different aspects of Power BI:

Performance analyzer: Find out how each of your report elements, such as visuals and DAX formulas, are performing. Using the Performance Analyzer, you can see and record logs that measure how each of your report elements performs when users interact with them, and which aspects of their performance are most (or least) resource intensive. This is accomplished by clicking a “Start recording” button and interacting with the elements you want to test. More info at Use Performance Analyzer to examine report element performance.

Click through for several other tools, which you can combine to get a better feel for how your environment is doing.

Comments closed