Dashboard Interactivity with Field Parameters in Power BI
In the May 2022 release of Power BI Desktop, Microsoft added a feature that is certain to make the lives of report developers and consumers better. Since then, this feature has taken the Power BI Community by storm, appearing in blogs by my heroes at SQLBI, by Reid Havens, and Ruth Puzuelo Martinez, to name just a few. That new feature, of course, is Field Parameters, and it’s a game-changer. Here’s why.
What are Field Parameters?
Suppose I have a simple model with two customers (John and Jane) who can purchase two goods (Guns and Butter).
In order to visualize how much of each product these two people purchase, I use a Customer Name slicer to control the filter context on a pie chart (ugh) that fixes Product Name on it’s legend.
That’s fine until my report consumers would rather view the pie chart by Customer, rather than Product. This is what Field Parameters is all about! Rather than restrict visuals to the values within individual fields, it allows whole fields or even distinct measures to become user-configurable parameters with slicers, so something like this.
How To Use Field Parameters
To use field parameters, since it is still a preview feature in Power BI Desktop, you must turn it on: File > Options and Settings > Options > Preview features. Check the Field parameters check box, and click Ok.
You’ll then be prompted to close and reopen Power BI Desktop, in order for field parameters to work. Let’s create our first parameter!
The Problem
Suppose our company executives like to visualize four (4) key performance indicators (KPIs) related to company sales: 1.) the number of orders, 2.) the quantity sold, 3.) the revenues from sales, and 4.) the total profit. I use real estate at the top of my report for KPI card visuals that summarize the last 12 months worth of data for each of these measures, and provide context on how these KPI’s have changed on a year-over-year basis. And I use the bottom portion of the report to visualize daily detail over time (left) and summarize by location(right) using a line chart and shape map, respectively.
The problem is that each of these visuals at the bottom (the line chart and map) only allow me to place one of the four KPIs in the measure well at a time (yes, line charts allow for a secondary y-axis, but suppose that wasn’t the case). So while the executives can see Orders over time and space, they can’t readily switch between measures.
Before Microsoft released Field Parameters, report developers could remedy this situation using complex modeling and DAX techniques. Now, it is much, MUCH easier and more effective with Field Parameters.
The Solution
To create a new field parameter, under the Modeling tab, select New parameter, and then Fields. When the Parameters modal pops up, specify a name for the table which will hold your parameters (I prefix all my measure and parameter tables with an “@”), select your parameters in the Fields pane, and check the “Add slicer to this page” box at bottom.
Clicking “Create” will add a new disconnected table with a measure containing the fields (and measures!) that you chose in the previous step. If you checked the box, it will also add a slicer containing the values of the measure. The final step is quite simple: add this new measure to the Y-axis well of the line chart, and the Color saturation well of the shape map.
Wow! The new slicer now lets the user control which KPI is displayed over time and space in the line chart and shape map visuals, respectively.
Do Better
But we can do better for the executives and allow them to find the signal from the noise by adding another field parameter to control the granularity of the x-axis. We’ll give it a name, and from our Date table we’ll select the Date, Month Year and Year fields. And we’ll add a slicer to the page.
As we did with the Selected Measure field parameter, we’ll add our new Selected Grain measure to the X-axis well of the line chart.
To finish, we’ll format the Selected Measure slicer so that it’s horizontally arrayed above our KPI cards at the top of the report, to give executives the experience of clicking on KPY card titles. And we’ll format the Selected Grain slicer, also horizontally, so that it’s placed under the x-axis of the line chart. Here’s the finished product:
