This page has 0 threads | Add post

Blogs

Finding our blogs useful? Please consider helping us to share more!

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 18 July 2018

In this blog

The problem
Solution 1 - use another visual
Solution 2 - Showing Data
Solution 3 - Use a measure and a card

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:

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.