Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
rmcconnell340
Frequent Visitor

Trying to Switch Hierarchies Dynamically with Two Columns

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.

 

rmcconnell340_1-1684368260472.png

 

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. 

rmcconnell340_2-1684368392597.png

 

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).

 

rmcconnell340_0-1684368196652.png

 

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! 

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@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...

View solution in original post

4 REPLIES 4
rmcconnell340
Frequent Visitor

That worked! Thanks @amitchandak 😀

rmcconnell340
Frequent Visitor

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

rmcconnell340
Frequent Visitor

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
Super User
Super User

@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...

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.