How to show current drilldown filters in Power BI Desktop
Some technique to remind users what levels they chose while using drilldown.

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.

The problem

Consider this chart:

Drilldown data labels

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:

Drilldown filters

If you don't have this ticked not much will happen!

Now try drilling down on the report and it should look like this:

Matrix Tables DrillDown Charts

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:

Tables Charts Drilldown

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:

Drilldown filters

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:

Bookmarks

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.

This blog has 0 threads Add post