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):
Visualisation with single filter selected (Shows the next level for S Series):
Visualisation with multiple filters selected (Shows the next level for S Series & T Series):
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.
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.
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.
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.
Step 5: Create a slicer using Parent 2 from the original ‘Model’ hierarchy table.
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.