Slicer and Visualization Hierarchy

[jtooke]

Hi,

I have a data set with a Region - SubRegion - Country hierarchy, with a slicer and visualizations using all 3 levels. My standard view shows all by Region with users being able to drill to lower levels of the hierarchy. 

What I am looking to accomplish is if a user expands the slicer hierarchy to view by level 2 or 3, when they make a selection I want the visualizations to update according to the next hierarchy level. So for instance, if a user expands EMEA in their slicer and selects Europe, the visualizations should show all of the countries within Europe.

Is this possible?

Thanks!

[danextian · Accepted solution]

Hi @jtooke 

This will require a disconnected table and another measure referencencing such. There are only two levels in the hierarchy below but notice that whenever a district is selected (level 1), the column chart automatically shows all schools within that district within the need to drilldown.

Please see the attached pbix.

[jtooke]

@danextian This is great, thank you so much! This is exactly what I needed.

[Kedar_Pande]

@jtooke 

Yes. Field Parameter + Level measure.

Field Parameter: Region → SubRegion → Country hierarchy slicer.

Measures:

Level = SWITCH(TRUE(),  HASONEVALUE(Region), 1,  HASONEVALUE(SubRegion), 2,  3)

Children = VAR L = [Level]
RETURN SWITCH(L, 1, Region = SELECTEDVALUE(Region),
2, SubRegion = SELECTEDVALUE(SubRegion),
TRUE())

Visual filter: [Children] = TRUE

If this answer helped, please click 👍 or Accept as Solution.
-Kedar
LinkedIn: https://www.linkedin.com/in/kedar-pande

[lbendlin]

In hierarchical visuals you can choose between Expand (showing the next level in addition to the current level) and drilldown (replacing the level with the next level).  

[jtooke]

Thanks @lbendlin - maybe I can clarify what I am looking for. I am looking to avoid the need for a user to drilldown at all - for instance in my Region - SubRegion - Country hierarchy slicer, when a user selects something from Region, I want the visualization to automatically update to show SubRegions under that Region. When a user selects a SubRegion in the slicer, i want the visualization to automatically update to show Countries under that SubRegion. And the visualization's default view should show the Region. 

[lbendlin]

when they make a selection

That is not something you can reliably probe.  Remember that in Power BI "nothing selected"  is the same as "everything selected"  and that you also need to distinguish between "selected","filtered", and "highlighted".

Educating your users on how to use these arrows is the better option in my opinion.

[Amar_Kumar]

@jtooke Not automatically with a hierarchy slicer.
But it is possible with a simple workaround.

• Create 3 separate slicers: Region, SubRegion, Country

• Use Country in your visuals

• Selecting:

  • Region - shows all SubRegions/Countries under it

  • SubRegion - shows all Countries under it

  • Country - shows only that Country
    No DAX needed, works as expected.

[jtooke]

Thanks @Amar_Kumar . Unfortunately this isn't quite what I need since I still need the Regional/SubRegional aggregation, where the default view should show each region, and when i select one region it should show the sub-regions for only that region. The countries should only show when one SubRegion is selected.

[Amar_Kumar]

@jtooke 

What you’re trying to do can’t be handled by slicers alone. Slicers only filter data; they don’t control which level of a hierarchy a visual should display.

The usual way to solve this is to let the visual decide the level based on what’s selected.

Set your visual up with the full hierarchy on the axis:
Region - SubRegion - Country
(do not use drill down)

Then create a small helper measure like this:

Show Level = IF(HASONEVALUE(Geo[SubRegion]) || HASONEVALUE(Geo[Region]),1,1)

Use your normal value measure for the visual, and add Show Level to the visual-level filters set to “is not blank”.

Behavior:

• No selection - visual shows Regions

• One Region selected -visual shows SubRegions for that Region

• One SubRegion selected - visual shows Countries

• Multiple Regions selected - stays at Region level

This keeps regional totals by default and naturally drills down based on the slicer selection, without users needing to click drill buttons.

[jtooke]

Thanks @Amar_Kumar ! I'm not sure i fully understand, I have my Region-SubRegion-Country hierarchy as the Values for my slicer, and the X-axis for my visualization with that measure created and added to the visualization filters. However, when I select anything from the slicer it doesn't alter the behavior at all