Forum Discussion
Slicer and Visualization Hierarchy
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!
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.
10 Replies
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 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.
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.
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.
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).
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.
when they make a selectionThat 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.
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
