Content_Cut_Icon Twitter_Brands_Icon

Drill Down Using Slicer in Power BI

Mode_Comment_Icon_white5
Alarm_Icon_1_white4 min
Book_Open_Solid_Icon
Executive Summary Key Takeaway in 30 Seconds

 

PA   powerbi-1 (1)

This article shows how to drill down to the next level in a visualization within Microsoft Power BI, when a filter is applied using a Slicer.

This could be applied even if multiple selections are made within the slicer.

For example – We have a car with 3 model series (L Series, S Series, T Series) in the slicer. Business requires to show the values at this level when no selection is made. Once a selection is made, the visual should show the next level in the hierarchy.

Visualisation without any filter selected (Shows the top level):

 

G1-1

 

Visualisation with single filter selected (Shows the next level for S Series):

 

G2-1
 

Visualisation with multiple filters selected (Shows the next level for S Series & T Series):


G3-1

 

In order to achieve the above in Power BI, follow the below steps,

Step 1: We have a ‘Model’ hierarchy setup in Power BI. The hierarchy was imported in Power BI using Octane connector from TM1. You could use any other hierarchy setup.

Lowest level is ‘model’ column with unique model names.


G4-1

 

Step 2: Create another table ‘Model Levels’ (used in this example) with 3columns from the above hierarchy in Step 1; 1st column being the Parent 2 & Parent 1; 2nd Column if Parent 2 then value is ‘L2’ else ‘L1’; and 3rd column being the lowest level model names.

 

G5-1

 

Step 3: Create a relationship between ‘Model’ & ‘Model Levels’ table with the lowest level model names. Select ‘Both’ in Cross Filter direction while creating the relationship.


G6
 
G7-1

 

Step 4: Create a measure ‘Sales_By_Model’ which calculates the actual value depending on the filter selections. If its selected, It drills to the next level, else, shows the top level.

 

G8-1

 

Step 5: Create a slicer using Parent 2 from the original ‘Model’ hierarchy table.

 

G9-1

 

Step 6: Create a visualisation using the Model Name (Parent 2 or Parent 1) from ‘Model Levels’ table created in Step 2 and for values, use the measure created in Step 4.

 

G10-1

 

New call-to-action

Gautam Narula
Written by

Gautam Narula

Gautam was a Senior consultant in Octane for 10 months.

Leave a comment

Got a question? Shoot!

Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua.

Get more articles like this delivered to your inbox