BLOGS BY TOPIC
BLOGS BY AUTHOR
BLOGS BY YEAR
Posted by Sam Lowrie on 20 July 2018
You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.
Showing drilldown levels currently applied
Drilldown on visuals in Power BI is a great feature that lets you get right down into the lowest levels of data. There is however a drawback for your users: the rest of this blog shows what this is, and various ways to overcome it.
Consider this chart:
I've drilled down through 3 levels and when I look in Filters I can see what I chose. The problem is that people viewing my report don't have this information available!
Solution 1 - use another visual
The most obvious solution to the above problem would be to have a matrix with the same columns as the visual getting filtered by the drill down. Turn this on via the Format tab:
If you don't have this ticked not much will happen!
Now try drilling down on the report and it should look like this:
While a table works, the matrix gives a nicer stepped effect (grouping each level together).
That works fine, but it is rather redundant having a duplicate visual. If the first one isn't doing its job then maybe its not the right one! Let's try another method which takes up less screen room.
Solution 2 - Showing Data
Built into PBI is the ability to see a table version of the current visual - and using it is a piece of pie (sorry). Just right-click on the visual and choose Show Data:
Show Data is always available unless the underlying data is generated by a measure.
The advantage of this over a standard table is the automatic concatenation of the drilldown levels into a single column:
The disadvantage is that PBI enters Focus mode, leading to the exclusion of all the other visuals on the page.
What would be even better is if we could combine the automatic filtering occurring in the Matrix with the concatenation taking place in the Show Data.
Solution 3 - Use a measure and a card
The best method uses a measure and a card to create a dynamic title. From the Modelling tab choose New Measure and paste this code:
Drilldown title =
"Values for Region: " & CONCATENATEX ( Regions, Regions[RegionName], "," )
& UNICHAR ( 10 )
& " and Centre types "
& CONCATENATEX ( CentreTypes, CentreTypes[CentreType], "," )
Change the names of the tables to match your own, and then place the measure a card to get something like this:
Make sure that Word wrap is turned on for the card.
Make sure to check out this blog, to understand what the above measure is doing and learn to return All Regions when not drilled down.