Simulate Hierarchies with Field Parameters in Power BI
Vložit
- čas přidán 30. 07. 2024
- 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)
docs.microsoft.com/power-bi/c...
📢 Become a member: guyinacu.be/membership
*******************
Want to take your Power BI skills to the next level? We have training courses available to help you with your journey.
🎓 Guy in a Cube courses: guyinacu.be/courses
*******************
LET'S CONNECT!
*******************
-- / guyinacube
-- / awsaxton
-- / patrickdba
-- / guyinacube
-- / guyinacube
-- guyinacube.com
**Gear**
🛠 Check out my Tools page - guyinacube.com/tools/
#PowerBI #FieldParameters #GuyInACube - Věda a technologie
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.
Great video with great timing, I was looking for something like this, thanks Patrick 👍
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!
Thanks a lot for this great video, Patrick! This is absolutely fantastic.
Wow, I was looking exactly for something like this. You saved me a lot of time :) Thank YOU!
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!
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.
Awesome solution! I love the new field parameter feature even more now.
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.
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.
Patrick, Awesome Vid! This is exactly what I needed! Field Parameters deserve their own T-Shirt for GIAC... I would buy.
Great feature, thank you for sharing.
AMAZING!!! I already know where I will use it!! thanks!!!!
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 :)
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.
This is GOLD, thanks
Awesome, Thank you so much
This is awesome!
Awesome! Thank you
Really amazing tips
Thank Your make my day
Love it!
Just recently I was doing something similar for comparison between databases but of the same metrics. So for each table I've had multiple metrics selected, multiply it by multiple tables and you get to number of approx 60 metrics which needs to be dynamically selected in pivot table.
So thanks to field paramateres I could have just add slicer with all of these 60 metrics and then bookmark each of the selection and for navigation then I've used bookmark panel.
This is another step forward, where the user can have this kind of grouping and I am definitely going to use it (also makes easier maintenance).
Cool workaround
Add one more int column to the end of that table and you can sort your slicer’s hierarchy as well!
That’s a great solution!
Great video. Is there a way to use field parameter in live connection?
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.
Fantastic
Thank you ! Is it possible in a table to simulate both Hierarchies AND differents KPIs with Fild Parameters ?
Hi Patrick! Great content. I wanted to ask, is there a way in Dax to know in what specific order a user selects field parameters?
My goal is to calculate running totals for each row subtotal, with field parameters on the rows
Simply Wow.....
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?
kudos !! Actually I figured out whta you are about to say. But You are intelligent !!
Nice video. I have a query about adding a calculated field in a column of hierarchy? Is it possible?
Is possible to choose the page that i want to open for a specific user, using the RLS and/ OLS?
Thanks for this,
I have a similar problem but m grouping the columns in the metrics,
So what is happening right now is all the measures are coming in both the groups
Like 6 different measures.
2 groups having 3 measures init
Created th same as you defined manual column after creating the field parameters
Any help would be much appreciated
Thanks
Q:- how to make my date parameter in slicer to let End User select specific date so it can show the data in specific day when using Stored Procedures ?
Hi Patrick, I think I have a related question. What if I have another Slicer that allows the User to select YYYY-MM (ie: 2022-12), in the field parameter, instead of Sales Category, i have measures that reads (Current Month, Month -1, Month -2). How do I change the measure names dynamically based on slicer selection to read (2022-12, 2022-11, 2022-10) ? Thank you for your help!
That's bananas!
When i try to export this matrix visual my values are missing(two fields in row and a field parameter in value of matrix)
Hi Patrick, hi Guys, is there a way to control the Field Parameters by the Browser Locale (e.g. en, de, fr)?
cool. very very cool
I’ll just want to be able to rename my parameters in a visual like everything else… it’s coming soon right?!
Indeed looks like an awesome feature, but from my experience it doesn't work very well when you want to visualize together the elements of different hierarchy levels: it doesn't respect the required sequence. What I mean is if I click on the "Product Hierarchy" and then on "Sales Continent" (so, I expect to see first the three elements of the "Product Hierarchy" and then "Sales continent") as the result I first get Sales Continent and then all sub-elements of the "Product Hierarchy"... However, if I select them one by one on sub-hierarchy level, everything works fine as expected. Have anyone experienced the same issue?
Hi Patrick,
I'm using Field Parameters quite a lot recently as they are quite flexible with little efort, One thing that is not so great is that, when you have a big report and a lot of measures you need to create few of field parameters, there is currently no option that I know of to "filter" parameters, lets assume I have 2 report pages and 10 Measures/fields, I would follow thing as you did on the video to group measures/fields into 2 groups, but I Cant display one group on the report page/ meaning users will always see both and In my case I wanted to limit the view to show only 1 group on certain pages
Well, you can filter but it's not working and there is weird error, not sure if you ever tested this option?
Patrick, Great video! I have a problem with the parameter and the bookmark. Imagine that a user that is in the dashboard, want to create a bookmark for Continent. And Another bookmark for Product. Why? He want to present a column bar, when select Product. So he personalize the report element, change it and save the bookmark as Product. And when select Continent, he personalize the report element to present Horizontal bar chat and save the bookmark as Continent (could be a map). But the he select his bookmark (either Product or Continent) the visual element don't show any data. Something happen with the parameter and the user need personalize it again. The bookmark erase the table paramenter into the visual element. Look like a bug. Could you do the same test? Thanks you!
How can we limit number of fields or measures to say 2,3, or 4 in dynamic selection. We don't want 100s of fields of meaaures in a chart.
How to create field parameters in Power BI Desktop (Report server) ?
Hi @Patrick - Matrix collapse each time one we select/unselect something in slicer. Again we need to expand the whole matrix. Any fix for this??
Experiencing same issue
@@sahibmukhija5063i have same issue.
Useless in case of chart and time series hierarchy to be used. eg. Year, Month, Day -> Year, Week.
Fix or workaround would be apprerciated.
Bananas!
That's 🍌🍌🍌