Hierarchical slicer - only allow user to select a single leaf?

[samloud]

Let me give you an example, and you'll get the question right away:

I have a geography dimension, that has a Country, State, City hierarchy. I want the user to only be able to select a single city. Yes, I can set the selection to 'Single select', but that allows the user to select California, which effectively selects all the cities in California. It even allows the user to select USA, which then selects all of the cities in the USA.

Is there a way to set the slicer up so that the user can only select a single *leaf* member?

[samloud]

Sure. Here I have selcted Belgium, which has selected all 4 leaf members. I want the user to only be able to select a single leaf member. I want to retain the hierarchy for navigation purposes, but it shoudl not be possible to select a country (and therefore its children), it shoudl only be possible to select an individual location

( I accept that this hierarchy only has 2 levels (Country --> Location), not 3 levels (Country--> State --> City) as in my initial description, but the underlying issue - only allow selection of a single leaf - is exactly the same)

[v-yangliu-msft · Accepted solution]

Thanks for the reply from @Uzi2019 , please allow me to provide another insight: 

Hi  @samloud  ,

As far as I know, Power BI doesn't support the following features for the time being, when clicking on Hierarchy A, it will automatically select all the information in the next Hierarchy B. Otherwise, it won't know how to judge how to display a certain value in the next Hierarchy B. So if you want to display a certain value in Hierarchy B, you need to go to Hierarchy B to click on it.

You can try to create a separate table for each level without relationship connection, so that the user can only go to each level table corresponding to the slicer to select, and will not affect the other slicer, and then in the creation of the measure, use if()/switch() function to and the main table of each level to equal, and then in the main table to get the expected results.

You can also submit an idea for it at https://ideas.powerbi.com/forums and wait for users with the same needs as you to vote for you to help make it happen as soon as possible.

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Uzi2019]

Hi @samloud 

can you elaborate with screenshot?? like how you want to display.