.png){kind=small}
.png){kind=small}
In this writing, I want to share how I use Field Parameter tables in my Power BI reports.
Field Parameters feature came out to the world as a preview feature in May 2022. I still remember that when it was first announced, everyone was so excited and many Power BI experts were sharing blog posts and videos about how to use this, when to use this, and how to use it more in an enhanced way in a datamodel in Power BI.
During almost 6 months, until today (14th November 2022), learning from many Power BI experts, I can create Field Parameters tables, and I can enhance my reports visualizations when report users want to see different measures in one visualization or different axis types in one visualization.
In the sample in this blog post, I created two Field Parameters tables, and I created a relationship between two Field Parameters tables, which I think it is more useful and meaningful than just having two Field Parameters tables with no relationships. In fact, they are tables and we can draw relationships between two tables or with any other tables in the datamodel if there are related columns in each table. Or, we can even try to create a new calculated column in newly created Field Parameters tables and then draw relationships.
Use case example
The first requirement from users was to show three measures in one visualization.
I have three measures that describe SUM, AVERAGE, and Year To Date. By using a Field Parameters feature, I can create a new table that includes these three measures, and then I can create a slicer by using each measure's name. Users can select a measure's name from a slicer to see SUM, AVERAGE, or Year To Date trend in a line chart. A month-year column is added into an x-axis. It looks something like below.
(The sample pbix file is attached in the very below of the blog post)
Datamodel
Select Sales Sum measure
Select Sales Average measure
Select Sales YTD measure
The second requirement from users was to show three different axis types, year / quarter / month, in one visualization. The calendar table contains a Year column, a Quarter-Year column, and a Month-Year column. By using a Field Parameters feature, I can create a new table that includes these three columns, and then I can create a slicer by using each column's name. Users can select a column's name from a slicer to see Year axis, Quarter-Year axis, and Month-Year axis in an x-axis of a line chart. It looks something like below.
Datamodel
Select Q-Year axis, and Sales Sum measure
Select Month-Year axis, and Sales Average measure
As shown above, because selection can be made from two slicers, there are nine different combinations to see trend lines shown in one visualization.
However, all nine trend lines do not provide meaningful insights for users. Sometimes, it makes users a little bit confused by letting users see unnecessary trend lines. After a discussion, users decided to see the visualization like below.
The third requirement
When selecting Sales Sum measure, a trend line shows by year.
When selecting Sales Average measure, a trend line shows by quarter.
When selecting Sales YTD measure, a trend line shows by month.
And only DAX measures slicer is shown on the report.
In this case, if [DAX measures parameter Order] column from DAX measures parameter table is related to [Columns axis parameter Order] column from Columns axis parameter table, then a single select from one slicer can dynamically influence the other slicer's selection. Furthermore, the other slicer is no more needed to be shown on the report page.
Datamodel
I purposely left the Columns axis parameter slicer to show how the each selection from the DAX measures parameter slicer influences the line chart. The Columns axis parameter slicer can be deleted from the report page, and still the same visualization is there to to seen.
To summarize, this was the starting of my new journey in creating a relationship between Field Parameters Tables in my datamodel. Not only I created relationships between two or more than two Field Parameters tables, but also I created new calculated columns in the Field Parameters tables and then draw relationships with other tables in order to use Field Parameters feature more dynamically.
I hope this helps Power BI authors start to have fun in creating relationships between Field Parameters tables and with other tables in the datamodel. And as a result, authors can describe dynamically reacting visualizations as per users' selections from the Field Parameters slicers.