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

                    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

                    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!