Is there a way to make a slicer switch between hierarchies based on another slicer's selected value?

[katari_123]

Greetings! Hoping someone can help me with this.

Here's the scenario:

I have a slicer called "Type" with values being 1 and 0

From the same table, I created 2 hierarchies:

• State Hierarchy: uses the fields "State", "City"
• City Hierarchy: uses the fields "City", "Zip Code"

Now I have another slicer that I'd like to toggle hierarchies (or parameter) depending on what "Type" value is selected.

Is there an easy way to do something like following?

SWITCH ( Type,

0, State Hierarchy

1, City Hierarchy)

It's completely doable with single field names but I can't quite figure out how to use it with hierarchies. I tried using Field Parameters, but it has to use that Parameter itself as a toggle, whereas I need my "Type" value to trigger the switch.

[amitchandak · Accepted solution]

@katari_123 , I think this can be done using a bookmark. This Switch between columns, and where we can use slicer value.

Another one is to create a field parameter. If needed, duplicate the field with a different name. And then create a custom column for your hierarchy and then use that to switch hierarchy 

Check the attached file for both options- Last two pages 

AxisNew = {
    ("Brand", NAMEOF('Item'[Brand]), 0),
    ("Category", NAMEOF('Item'[Category]), 1),
    ("Sub Category", NAMEOF('Item'[Sub Category]), 2),
    ("Name", NAMEOF('Item'[Name]), 3),
    ("State", NAMEOF('Geography'[State]), 5),
    ("City", NAMEOF('Geography'[City]), 4),
     ("City G", NAMEOF('Geography'[City]), 6)
}

[Kedar_Pande]

@katari_123 

Use Field Parameters for the hierarchies and place that param slicer as toggle.

Use Bookmarks or Buttons to swap visuals showing different hierarchy slicers based on the "Type" slicer selection.

Create a disconnected table (Type values) + measures + layered visuals that show/hide based on selection.

[Jihwan_Kim · Accepted solution]

Hi,

I am not sure if I understood your question correctly, but I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file.

One of ways is to create an additional column (and name it as "Type") in the field-parameter, something like below.

[katari_123]

Thanks so much, this solution worked for me! The examples were really helpful. I was also able to create a relationship between the field parameter and table to filter the data, which was exactly what I was looking for 😊

[amitchandak · Accepted solution]

@katari_123 , I think this can be done using a bookmark. This Switch between columns, and where we can use slicer value.

Another one is to create a field parameter. If needed, duplicate the field with a different name. And then create a custom column for your hierarchy and then use that to switch hierarchy 

Check the attached file for both options- Last two pages 

AxisNew = {
    ("Brand", NAMEOF('Item'[Brand]), 0),
    ("Category", NAMEOF('Item'[Category]), 1),
    ("Sub Category", NAMEOF('Item'[Sub Category]), 2),
    ("Name", NAMEOF('Item'[Name]), 3),
    ("State", NAMEOF('Geography'[State]), 5),
    ("City", NAMEOF('Geography'[City]), 4),
     ("City G", NAMEOF('Geography'[City]), 6)
}

[katari_123]

Thanks! The field parameter solution worked for me. Thanks for including examples, they're really helpful. I'll have to test out the bookmark approach next time.