562 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owls only (no freelancers)
Almost no cancellations
We have genuine integrity
We invoice after training
Review 30+ years of Wise Owl
View our top 100 clients
|How to show current drilldown filters in Power BI Desktop|
|Some technique to remind users what levels they chose while using drilldown.|
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!
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.
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.
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.
25 Aytoun Street