Simulate Hierarchies with Field Parameters in Power BI

Struggling with field parameters and hierarchies in Power BI? They just don't work! Patrick has an approach that looks to simulate hierarchies by using field parameters in this way.

Let report readers use field parameters to change visuals (preview)


*******************

Want to take your Power BI skills to the next level? We have training courses available to help you with your journey.


*******************
LET'S CONNECT!
*******************


***Gear***

#PowerBI #FieldParameters #GuyInACube

[EmileDavis]

This is great Patrick! I used this method to allow the user to select a different order of fields on a pivot. Some users want to see data by Customer industry and then country. Other users want to see by the reverse order (Country then customer industry). To get this, I created the parameter with just the two fields. Then I edited the DAX to add those two rows but in reversed order. Finally, I did your trick of adding the fourth column to specify which order to use. Worked like a charm! Just wish that all your great work-around solutions weren't necessary.

[sjoerdnefkens]

With this extra column, you can also make a relationship with other tables. So you can make a slicer selection table, first column the selection, and additional columns for each field parameter table. Connect the table to multiple field parameter tables. now the user can select the x -axis and legend at the same time. I use it for switching between month and week and the x-axis. In that case the legend need also be changed from year to iso-year.

[prasannajeetrout]

Great video with great timing, I was looking for something like this, thanks Patrick 👍

[nomap]

Hey man, I just got hired and my boss asked me to delete the repetitive reports and consoludate them as one. You saved my first project :)

[drjenp]

Another way I identified to achieve this is to use the groups function by right clicking the parameter in the fields pane. Good to know there is another way to do this!

[oviktoor]

Thank you, my friend, in just a few minutes you managed to help me solve a problem that had been bothering me for a few days. Thank you!

[reiffaulkner]

Thank you, this is great! I was able to take this and combine it with RLS to set a starting level depending on the user's role. For example a user in the "National" role will start by look at all states and can drill down from there. A user in the "State" role can look at all the counties in their state only and a "County" user can look at all the different cities in that county. This is a really helpful tool.

[Designingne]

What I found was if selections were made out of order across different parent sections, the report order would get messed up after the 3rd or 4th parameter selection. This doesn’t happen if everything is in the default single column.

[ThePPhilo]

This is great, looking forward to trying it out.

Another option could be to use selectedvalue and switch to manually chain field parameters together. Would potentially give you more flexibility as you could specify each level of the hierarchy depending on what the user selected.

For example, user selects hierarchy 1 and all the hierarchy levels are picked accordingly

You would need a separate measure for each level though.

[ugocuendet]

Thanks for the video, it really helped me with my job.

[calinranger]

Hi Patrick! I apologize if this was asked already but what would you do if you want the same dimension to appear in both hiearchies? Continuing on your example, what if I want to add Product Name to the Sales Continent Hiearchy to see product sales for particular region?

[jaromgleed]

Patrick, Awesome Vid! This is exactly what I needed! Field Parameters deserve their own T-Shirt for GIAC... I would buy.

[michabednarczyk]

Wow, I was looking exactly for something like this. You saved me a lot of time :) Thank YOU!

[gerry]

Thanks a lot for this great video, Patrick! This is absolutely fantastic.

[ryanfisher]

Awesome solution! I love the new field parameter feature even more now.

[daneboy]

Add one more int column to the end of that table and you can sort your slicer’s hierarchy as well!

[Lebkuecher]

Field Parameters can be a game changed for some reports! I used them, and then grouped the measures to create a hierarchy (didn't know you could just add a fourth value to the DAX), by using this, I was able to use the chiclet slicer to create "buttons" for the users to choose the metric category the want to view, rather than having 1 really wide matrix with an measures showing at once. So much cleaner and user friendly! Plus you could use Field Parameters to avoid bookmarks or page navigation.

[josedanielherreraochoa]

Thank you a lot!!! This explanation was very helpful

[HamzaGhannem-qt]

thanks for the videos, i have a question, is the Hierarchy following user selection order? for example if you select Product Name, then Subcategory and then category, it should be ordered like you defined in the hierarchies table right?? otherwise how to order the levels!

[fintech]

Great video. Is there a way to use field parameter in live connection?