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

Welcome back to this week’s edition of the Power BI blog series.  This week, we conclude our series by looking at how to use a ‘Select all’ option in the slicer referencing M parameters. 

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.

In Part 1, we created parameters and amended an M query to reference the parameters:

Last time, we created a new table for each parameter, linked them up and referenced them in a slicer:

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 this week.

Consider the following scenario.  Within the Model tab of Power BI Desktop, let’s have a field called Country (list of countries) that is bound to an M parameter called countryNameMParameter:

Notice that this parameter is enabled for Multi-select but not enabled for ‘Select all’.  When we enable the ‘Select all’ toggle, we’ll see an enabled input called ‘Select all’ value:

The ‘Select all’ value is used to refer to the ‘Select all’ option in the M query.  This value is passed to parameter as a list that contains the value we defined for ‘Select all’.  Therefore, when we are defining this value or using the default value, we need to make sure that this value is unique and does not exist in the field bound to the parameter.  Once we have set the value or used the default value for ‘Select all’, we need to update the M query to account for this ‘Select all’ value.

To edit the M query, we launch the Power Query Editor using the ‘Transform Data’ button on the Home tab, and then select ‘Advanced Editor’ in the Query section:

In the Advanced Editor, we need to add a Boolean expression that will evaluate to TRUE if the parameter is enabled for Multi-select and contains the ‘Select all’ value (else return FALSE).  For our example that would look like this:

Next, we will need to incorporate the result of this ‘Select all’ Boolean expression into the source query.  For our example, we have a Boolean query parameter in the source query called includeAllCountries that is set to the result of the Boolean expression from the previous step.  We then use this parameter in a filter clause in the query, such that FALSE for the Boolean will filter to the selected country name(s) and a TRUE would effectively apply no filter:

For reference here is the full query employed:

Once we have updated the M query to account for this new ‘Select all’ value, we can use the ‘Select all’ function in slicers or filters:

When we allow report readers to dynamically set the values for the M query parameters, they may be able to access additional data or trigger modifications to the source system using injection attacks, depending upon how the parameters are referenced in the M query and what values are passed to that parameter.

For example, let's say we have a parameterised query Products:

We may have no issues with a friendly user who passes an appropriate value for the parameter, for example, Games:

However, an attacker may be able to pass a value that modifies the query to get access to more data, for example, 'Games' //:

In this example, the attacker can get access to information on games that have not been released yet by changing part of the query into a comment.

To mitigate the security risk, it's best to avoid string concatenation of M parameter values within the query.  Instead, consume those parameter values in M operations that fold to the source query, so that the M engine and connector construct the final query.  Alternatively, if available, make use of a parameter passing mechanism built-in to the source query language and connectors.  For example, Azure Data Explorer has built-in query parameter capabilities that are designed to protect against injection attacks.

As examples:

• Example using M query's filtering operations:

• Example declaring the parameter in the source query (or passing the parameter value as an input to a source query function):

There are some considerations and limitations to consider when using dynamic M query parameters:

• a single parameter cannot be bound to multiple fields nor vice-versa
• aggregations are not supported with the feature
• Row Level Security (RLS) is not supported with the feature
• parameter names cannot be reserved words in DAX nor contain spaces.  Appending ‘Parameter to the end of the parameter name can help avoid this limitation

if the parameter is Date/Time data type, cast it within the M query as     

• if using SQL sources, there is a confirmation dialog every time the parameter value changes.  This is due to a security setting: ‘Require user approval for new native database queries’.  This setting can be switched off in the Security tab of the Options dialog in Power BI Desktop
• the following are unsupported out-of-box parameter types:

                    o   Any

                    o   Duration

                    o   True/False

                    o   Binary

• unsupported filters:

                    o   Relative time slicer or filter

                    o   Relative date

                    o   Hierarchy slicer

                    o   Multi-field include filter

                    o   Exclude filter / Not filters

                    o   Cross-highlighting

                    o   Drill-down filter

                    o   Cross drill filter

                    o   Top N filter

• Unsupported operations:

                    o   And

                    o   Contains

                    o   Less than

                    o   Greater than

                    o   Starts with

                    o   Does not start with

                    o   Is not

                    o   Does not contain

                    o   Is blank

                    o   Is not blank.

This concludes our series on composite models and dynamic M parameters.

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