When filtering and interacting with a direct query report end users want the best of both
worlds snappy performance and access to more of the raw data for those looking for more query
performance optimizations without compromising on report interactivity we're introducing a new
public preview feature Dynamic M-query parameters this new feature allows M query parameters to be
dynamically updated by the report consumer based on their filter or slicer selections
by giving authors control over how end users filters gets incorporated into the source query
they can optimize performance of the query and also allow end users to freely interact with
their port using slicers or filters let's take a look at the end user experience you'll see that
the end user experience will feel the same as a standard report meaning that by simply changing
a filter the query parameter is automatically updated based on their selection previously
this was something that was not possible at all now let's take a look at how we can set this up
as the report author to get started we'll need to first turn on the preview feature so
be sure to go to preview features and select dynamic mcor parameters note that you may want to
check out the documentation in case your scenarios are not supported because these are m-based data
source scenarios sources such as sql server would not be supported as that relies on native query
not mquery to pass the parameters so once you have this feature switch enabled let's see the
next step of setting up the parameter so i'm going to go over to my custom query and hit edit query
now let's take a look at the parameters i've set up here
so i have three different parameters so i'm going to go to manage parameters
and you'll see here that we have three that are different types so two of them are both
date time and then one of them is text so let's use end time parameter as our example
so i'm going to go into the advanced editor to see how the parameter was referenced in the query
you'll see here that by referencing the parameter in the query i can optimize for performance
because i have full control over how this filter gets incorporated into the source query
notice that by filtering before i do a more expensive operation such as the make series count
i'm helping improve the performance now let's take a look at the columns that i
use to bind to the parameter so you'll need to create columns that can be used as values as
inputs to the parameter so let's take for example the end date time so i've created a very simple
table here that uses the calendar function to just generate the date range which i'm interested in
so once i have this column created let's switch now to the modeling tab so in the
modeling tab let's make sure that we select the column that we want to map to the parameter
and then select advanced and if you scroll down you'll see this option that says bind to parameter
and let's select the appropriate parameter notice that you'll
only see the parameters that match the exact data type as the field you're trying to map to
so in this case we just see the start time and end time parameter which can be mapped to the
to the current field keep in mind that parameters can only be mapped one to one
so if you have two date scenarios you'll need to create two separate columns for each parameter
and you'll also notice down here there is a multi-select option so by default it's turned
off but if you have a multi-select scenario you can go ahead and turn this on but be sure
that how you reference the parameter accommodates for that multi-selection so now that we've mapped
the parameter we can now reuse the mapped fields anywhere as a filter or a slicer or even a drill
through but again keep in mind that because this is single select in this scenario you'll want to
limit it to a single select slicer as you don't want to cause errors for your end user and now we
can try out some values and you see here that as the query updates it's also updating the parameter