Power BI Blog: Composite Models and Dynamic M Query Parameters Part 2

Welcome back to this week’s edition of the Power BI blog series.  This week, we link the M parameters to tables and reference them in a slicer. 

Microsoft have added support recently for Power BI datasets added that have Dynamic M Query Parameters defined.  Now, we may create a composite model on such datasets to enrich or extend them.  With Dynamic M Query Parameters, we can let report viewers use filters or slicers to set values for an M query parameter.

Last time, we created parameters and amended an M query to reference the parameters:

This week, we will create a table with a column that provides the possible values available for that parameter.  This will allow the parameters to be dynamically set based on filter selection.  In this example, we want our StartTime parameter and EndTime parameter to be dynamic.  Since these parameters require a Date/Time parameter, we want to generate date inputs that may be used to set the date for the parameter.  We start by creating a new table in Power BI:

Here's the DAX to create the first table for the values for StartTime parameter:

This is the DAX for the second table created for the values for EndTime parameter:

We use a different column name that is not in a table.  Otherwise, if names are duplicated, the selected value will be applied as a filter to the query.

Now that the tables with the Date field have been created, we can bind each field to a parameter.  Binding the field to a parameter essentially means that as the selected value for the field changes, the value will get passed to the parameter and update the query where the parameter is referenced.  Therefore, to bind field, we go to the Modeling (sic) tab, select the newly created field, and then go to the Advanced properties (noting that the column ‘Data Type’ should match with the M parameter type):

We select the dropdown under ‘Bind to parameter’ and select the parameter that we want to bind to the field:

Since this example is for a single-select value (setting the parameter to a single value), we want to keep Multi-select set to No, which is the default:

If we were creating an example that required multi-selection (passing multi-values to a single parameter), then we would toggle the switch to Yes and ensure that our M query was set up properly to accept multiple values in the M query.  Here's an example for RepoNameParameter, which allows for multiple values:

We repeat these steps for other fields to bind to other parameters:

Finally, we can reference this field in a slicer or as a filter:

If the mapped column is set to No for Multi-select, we either use a single select mode in the slicer or use a single select in the Filter card.

There are additional steps if we want end users to be able to use the ‘Select all’ option in the Slicer or Filter card, which we will look at next time.

Check back next week for more Power BI tips and tricks!