Trying to Switch Hierarchies Dynamically with Two Columns

[rmcconnell340]

Hi,

I am trying to create a filter that can change with a toggle based on two different hierarchies I have for the same dataset. I could separate the hierarchies and create a separate column to toggle between the two in a different table but this created a VERY slow data load compared to when I kept the hierarchies in the same table.

I have the two hierarchies above (right now I am just using department, but there is also a category and subcategory hierarchy for each). One hierarchy is what my company uses, the other is what the client uses. 

In my report selections page, I have a filter for one of the hierarchies, but I would like this to be able to toggle between one hierarchy or the other hierarchy with a filter. 

So far the only solution I have come up with is to use a Field Parameter to filter the table and then use the Filter Pane on the side to filter down the department, category, and subcategory of one hierarchy to the other. The only issue with this is that the Filters don't filter down so if I select the GROCERY department it won't filter to all the GROCERY categories. So really I would like a toggle that than shows me all the departments that are under department and then when I switch it I can see all the departments under department_ext within a filter (not just in the table like Feild Parameter does).

Is there a way to toggle between two columns in the same table so that I can just filter the report on one column vs the other?

Thanks! 

[rmcconnell340]

That worked! Thanks @amitchandak 😀

[rmcconnell340]

@amitchandak I will look into it and see if it works. Thank you! 

[rmcconnell340]

Hi @amitchandak! That is what I did in the photo above. The Feild Parameter filters between the two hierarchies but the issue is that I need to then filter the hierarchies with another filter and the Parameter does not allow that. 

For instance, the Parameter allows me to filter between "department" and "depatment_ext" but does not allow me to filter to GROCERY, FROZEN, ALCOHOL, etc which are specific departments I need to filter the report by. 

[amitchandak · Accepted solution]

@rmcconnell340 , Check if this option of field parameter can help, but when you toggle, the other slicer value will be lost

Create dynamic slicers using field parameters
https://powerbi.microsoft.com/en-za/blog/power-bi-november-2022-feature-summary/#post-21321-_Toc1175...