When I was working for a customer two years ago, they requested to use the data hierarchy (Year, Month, Day) in a graph in Power BI. No problem, I included the hierarchy on the X-axis of multiple graphs and showed them how to use the drill-down option. But then they said: I don’t want to do this individually, I want to drill down once, and all other graphs should drill down as well. At that moment I was thinking about using bookmarks, but one of my colleagues suggested using an unpivoted date table. We then could select the grain, and all graphs were using this grain.
With the new field parameter option this solution is not needed anymore, and it makes the whole experience a lot better! Need an extra level? Just add it to the calculated table! In this blog, I’ll show you some options I like to use the new field parameter.
The first option is to solve the problem described in the intro. For this I create a new Field Parameter and select the Year, the Month name, and the Date:
I keep the “Add slicer to this page” selected, but you’re also able to add the slicer later if you deselect it. The calculated table looks like this:
You got the name of the selected column, the column itself, and the order. Now I can give the fields another name if I like:
Now when I use the “Datehierarchy” on my X-Axis and select the year, I see the year. But when I select the Month Name, both graphs will go to the month level and when I select Date, I will see everything on a daily level:
I really love how the title also changes, based on the selected level.
Now if we want to also add the weeks to this parameter, we can very easily change the DAX of the table:
Here you can see very well how the order number works. Even though weeks are added at the bottom, with the order number it’s still before the Date. Still, I would suggest keeping the order also in the DAX as expected 😉.
Select by which categories your measure is sliced
My current customer is moving from Tableau to Power BI and they had a very nice matrix in which they could select the fields they want, and the numbers were only sliced by those fields! With the field parameter, this is now also possible!
For this option, I will select some describing fields that we will use to slice our Gross margin by:
When I add the Productfields column to my Matrix, this is what I get:
Now, what If I’m only interested in the Sales by Color and Size? I just selected these values:
But what if I want to see it just the other way around? That’s the beauty of this option! Not a problem! Instead of selecting color first and then Size, we select Size first and then Color:
Now, this was something they couldn’t do in Tableau!
Show the total number or the percentage.
Now the last option I want to show, is how you can easily switch between measures in your graph. Before, we had to do some stuff with bookmarks, hiding and showing values, and adding buttons. Because Microsoft improved the way bookmarks work, and the flexibility it gives you, it might still be your preferred option. But I still like to show you how incredibly easy it is to do now:
These two fields are measures, the first one counts the number of rows, and the other calculates the percentage of the total. Now, if you have enough space, it’s easy to show both of the values in one table or matrix:
But, if your space is limited or you need the measure in a graph, you can now easily select the measure you want to use:
I think it’s really amazing how the labels and Y-axis automatically change to the correct format of the measure.
There are so many sources on the internet that show the amazing functionality of this new feature, but hopefully, I showed you something new as well!