On 23 May 2024, I had the pleasure of presenting a session titled Dissecting Field Parameters to Power BI Barcelona User Group. Thank you Bernat Agulló for inviting me!

In this post I wanted to post a quickfire list of the key points I presented! 😊

The PBIX containing the examples is here:

1. Simplest possible field parameter

In its simplest possible form, a field parameter can be a single-column table. The only requirements for the column are:

1. It contains valid fully-qualified column or measure references as text.
2. It has an Extended Properties JSON property called ParameterMetadata set to value {"version":3, "kind":2}.

For example, this calculated table in my sample PBIX is a valid field parameter:

Product Field Parameter = 
{
    NAMEOF ( Product[Category] ),
    NAMEOF ( Product[Subcategory] ),
    NAMEOF ( Product[Model] ),
    NAMEOF ( Product[Product] )
}

However, such a bare-bones field parameter is not very pretty! It has no sort-by column and no “display name” column, both of which would normally be created automatically when creating a field parameter using the Power BI Desktop interface.

2. Field parameter tables can be created using calculated tables or Power Query

The Power BI Desktop interface creates field parameters using calculated tables by default. A good reason to use this method is that the auto-generated calculated tables use the DAX function NAMEOF to ensure that column and measure references update automatically if any names change.

However, there can be situations where it is convenient to create a field parameter table using Power Query. One such situation is where we want a field parameter to update automatically to include all columns of a table on each refresh. This might be useful when a model is still under development and the columns are subject to change.

With this method, it is up to the model author to ensure that the contents of the field parameter table remain valid, and that the Extended Properties, Group By Columns and Sort By Column properties are correctly set (see my previous post for details).

To give an example, suppose we want to create a field parameter that allows selection of all columns of Customer:

Using the below code, we can create a field parameter table in Power Query that has the same structure as the table that would have been created using the usual interface:

The resulting table looks like this:

Once all the column properties are set, this table behaves exactly the same as a field parameter table created using the Power BI Desktop interface.

3. Controlling order of field selection

By default, when multiple field parameter fields are selected on a slicer, the fields are added to visuals in the order of selection.

If we want to instead add fields to visuals in the default sort order regardless of order of selection, we can create a copy of the field column. This can simply be a calculated column equal to the original column.

Here is an example of the Customer Field Parameter table with a calculated column added:

Customer Field Copy = 
'Customer Field Parameter'[Customer Field]

Here is a demonstration of how fields appear in a visual using the two methods of selection:

4. Allowing “no selection” for a field parameter

One limitation of field parameters is that there is no way for users to select “no fields”. This is because slicers do not permit “no selection” to be made, since an unfiltered slicer places no filter on the column.

However, we can work around this by adding an invalid option to a field parameter. The invalid option must be contain a nonempty text value in the field column that does not correspond to an actual column or measure reference.

Here is Customer Field Parameter v2 which includes a “No Selection” option:

If we simply make “No Selection” available on a slicer in the same way as any other value, it must be the only value selected in order for “no selection” to apply.

However, we can make this more user-friendly by:

1. Hiding the “No Selection” option on the slicer using a visual-level filter. If no selection is made on the slicer, the hidden “No Selection” option is in fact included in the filter context (since no filter is applied by the slicer).
2. Creating a special “flag” measure that we can apply as a visual-level filter to any other visuals displaying the field parameter.

The “flag” measure returns 1 for field parameter values that should be displayed. It is constructed so that if “No Selection” is one of the selected values, then 0 is returned for all other field parameter values which are therefore hidden.

Here is the measure Customer Field Parameter Flag:

Customer Field Parameter Flag = 
VAR NoSelectionSelected =
    CALCULATE (
        "No Selection" IN
            SELECTCOLUMNS ( 'Customer Field Parameter v2', 'Customer Field Parameter v2'[Customer Field] ),
        ALLSELECTED ()
    )
VAR CurrentField =
    MAX ( 'Customer Field Parameter v2'[Customer Field] )
VAR ResultBoolean =
        IF (
            NoSelectionSelected,
            CurrentField = "No Selection",
            CurrentField <> "No Selection"
        )
VAR Result =
    INT ( ResultBoolean )
RETURN
    Result

This measure is then applied to the target visual (in my example a table) by adding a “Top 1” filter on the column 'Customer Field Parameter v2'[Customer Field] by value of the measure Customer Field Parameter Flag.

Here is the behaviour in the report:

5. Dynamic field parameter selection

We can also make use of “flag” measures to allow dynamic field parameter selection in more general situations.

Suppose we have a field parameter Date Field Parameter that allows selection of 'Date'[Year] or 'Date'[Start of Month] to display on the axis of a visual.

Date Field Parameter =
{
    ( "Year", NAMEOF ( 'Date'[CalendarYear] ), 0 ),
    ( "Start of Month", NAMEOF ( 'Date'[Start of Month] ), 1 )
}

Let’s suppose that we want to display months on the axis of a column chart only if a single year is visible, otherwise display years. We can create a measure Date Field Parameter Flag Binary as follows:

Date Field Parameter Flag Binary = 
VAR CurrentField =
    MAX ( 'Date Field Parameter'[Date Field Parameter] )
VAR ResultBoolean =
    IF (
        HASONEVALUE ( 'Date'[CalendarYear] ), -- If one year filtered
        CurrentField = "Start of Month", -- then show Month
        CurrentField = "Year" -- else show Year
        
    )
VAR Result =
    INT ( ResultBoolean )
RETURN
    Result

This measure returns 1 only if the visible field parameter value is the one we want to display.

We can then apply a “Top 1” filter to the target visual on the column 'Date Field Parameter'[Date Field Parameter] by value of Date Field Parameter Flag Binary:

The axis of the column chart then changes based on the number of visible years:

Conclusion

That concludes this short foray into field parameters.

I’m sure there are many possible intriguing variations and improvements on what I have presented above.

For example, here is a great post by Kane Snyder exploring how clever data modelling can be combined with field parameters.

I would be interested in hearing how you have used field parameters and any interesting discoveries you have made!