Power BI – Field Parameters
BY: David Rohlfs
If you are familiar with parameters, then you might’ve heard of a Field Parameter. Field parameters are parameters that contain entire fields of values, and they have some interesting properties.
As of writing this blog, the field parameter is still in the preview features. I will start the Walkthrough with the steps on how to turn on this preview.
In this blog we are going to go through when you might want to use a field parameter in Microsoft Power BI, some common problems that you may find with field parameters, a walkthrough demonstrating how to build a field parameter, and a few ways that field parameters interact with visuals and other slicers.
When to use it
Field parameters are a cool feature because they are going to allow the end user to have a lot more power in how they utilize a report. Power BI is very good at creating features that take a little more dedication to set up and create but save time in the future, and this is a definite example of that.
Before getting too far into field parameters, know that for some visuals the default options will be the first option in the list. This is just to keep information on the visual and does not affect how the field parameter interacts with the visual.
If you choose to use this feature, you need to have all the fields within the field parameter connected in the data model using active relationships. If you do not, then you will encounter visuals that show total data, or throw error because there is not an active relationship.
Because this feature gives more power to the end user than many other features, it is important to think about who you are building this report for. If the end user for a report is someone who isn’t good with technology and dislikes other features that are more interactive, then the field parameter is not a great idea. But if the end user is someone who likes to explore their data and pack a lot of data into a few pages, the field parameter might be a great feature.
One important property about field parameters is if they are being used in a slicer, they do not affect other non-field-parameter slicers. Normally, unless you are using the edit interactions interface, choosing an option on one slicer will change the options of other slicers on a page. Here is an example of this. The slicer named “Field Parameters” is the field parameter, and the other slicer is a normal slicer. Notice that the second image has options selected on the Field Parameter slicer, but you still see all the original options in the slicer.
If you are building reports using this feature, it will be important to educate your end user about this feature. It is always important to remind yourself about what the end user will think when they see a page. And in this case, they need to know about how the field parameter feature will affect the report. This is important with any largely interaction-based feature, but especially features that highly affect a visual.
When you create a field parameter, it is creating a DAX table that is based on a set of code created. Thankfully, the code is simple to understand, so if you need to you can change it manually. Here is an example of what the code is:
You delete a field after creating the field parameter.
This is very likely and is an easy fix from a report builders’ point of view. Here is an example of where I started. The top Product name is from a dimension table that I am about to delete.
Once I delete the dimension table, this I what happens:
All that I need to do is delete that line of code in the DAX formula and click the check mark in the top left corner.
Error with Field Relationships
If you have a field parameter that contains tables that have not been connected via the data model, then you will start to see errors in your visuals. All the fields in a field parameter must be connected by relationships. To learn how to fix this, you can read through EPM Strategy’s blog on Basic Relationship Building linked below.
Before we get into the field parameter walkthrough, I want to make sure that you know how to turn on the ability to use a field parameter. You need to go to File, Options and Settings, Options, Preview Features, then check mark the Field Parameter option.
This walkthrough is going to show you how to create a field parameter, then several pages utilizing this feature. These pages will have different demonstrations on how the field parameters affect a report.
Creating a Field Parameter
To start, we need to be in the report view and select the Modeling ribbon. In this ribbon you will see a button for New Parameter. Click the dropdown on this and select Fields.
Now you will enter the pop-out for the field parameters. You can change the title of your parameter and select the appropriate fields that you want within this parameter. In this pop-out, there is an option to add a slicer to the current page. For this demonstration I unchecked this option.
Then you can click Create in the bottom right corner, and you should see a new table appear in your fields list. Each field parameter that you make will create a new measures table in your fields list.
Now we can create a slicer or table with this field parameter as you normally would in Power BI.
For the first example I want to show you how a field parameter acts with a visual in Power BI. Here is the initial page:
When I select an option in the Field Parameter slicer, it alters the visual because I placed the Field Parameter on the Y axis of the visual.
This second page demonstrates that a selection in the Field Parameter slicer does not affect the normal slicer beneath it.
This third page has a second slicer that shows the slicer options for the field parameter selected. You could think of this as a drilled down version of the initial field parameter slicer. The demonstration of how to set this up is with the preview options for the report view enabled, so it may differ if you are using an older version.
To start you can just copy and paste the field parameter slicer. Then go into the field options select the greater than symbol and reselect the field parameter field.
When you select an option on the original field parameter slicer it will show the options for that field in the second slicer.
This fourth page is showing what happens to a second field parameter slicer (Demonstration 3) if you select multiple options from a field parameter slicer.
If you select two or more options from a field parameter slicer, with a secondary slicer on the page, you will create a hierarchal slicer. Here is an example where I selected Product Name then City. Notice that whatever order I select the slicers in determines the order that the secondary slicer creates the hierarchy.
Finally, I want to show you what you can do with a field parameter slicer and a table or matrix. A table or matrix has a different ability in that you can add fields to the table or matrix by selecting options in the field parameter slicer. This essentially enables the end user to create their own table.
Here is what the page looks like with no options in the field parameter selected. Notice that the table is defaulting to all the fields as being active.
And here is what the page looks like after I select options in the slicer.
All you need to do is put the field parameter on the columns (or rows if matrix) of your table and you have a fully interactive visual!
Every time that a new feature is added to Power BI it opens a new world of possibilities for both report builders and end users, and the field parameters feature is a great example of this. As the Data Analytics and Business Intelligence needs grow for businesses, the job of a report builder becomes more demanding and labor intensive. But having a feature that gives more power to the end user helps this burden and becomes a must have in any report.
There are a lot of concepts in this blog that EPM Strategy has covered in previous blogs. If you found a method or reference that you did not know, I suggest reading through some of the blogs linked below.
Links Related to This Article: