If you’re building DAX queries using Power BI Report Builder you might notice something that looks like a new DAX function called RSCustomDaxFilter. For example, here’s a simple DAX query built from the Adventure Works Tabular demo database, with one measure and one parameter built Calendar Year:
Note that the “Enable Multi Value Parameters” option has been selected. Here’s what you’ll see if you view the text of the DAX query in the Query Designer:
[sourcecode language='html' padlinenumbers='true'] EVALUATE SUMMARIZECOLUMNS( RSCustomDaxFilter( @DateCalendarYear, EqualToCondition, [Date].[Calendar Year], Int64 ), "Internet Total Sales", [Internet Total Sales] ) [/sourcecode]
But what is RSCustomDaxFilter? If you run your report and see what happens on the server using SQL Server Profiler, here’s the query that actually gets run (in this case I selected the years 2013 and 2014 for the parameter):
[sourcecode language='html' ] EVALUATE SUMMARIZECOLUMNS ( FILTER ( VALUES ( 'Date'[Calendar Year] ), ( 'Date'[Calendar Year] = VALUE ( "2013" ) ) || ( 'Date'[Calendar Year] = VALUE ( "2014" ) ) ), "Internet Total Sales", [Internet Total Sales] ) [/sourcecode]
What has happened is that RSCustomDaxFilter has been replaced with an expression using the DAX Filter() function that implements the filter on the selected years; it’s just a placeholder for a dynamically-generated DAX expression that is substituted in at runtime.
Why is it needed? Handling multi-value parameters is difficult in DAX when you don’t know how many values are going to be passed to the parameters (it’s a subject I’ve blogged about here and here) and some kind of dynamic code generation is a reasonable solution to this problem.
There is one drawback with this approach though – it can generate a DAX query that is too long to be executed. Here’s the error message you’ll see:
The specified query is too complex to be evaluated as a single statement.
I’m not sure what the maximum length of a query is in DAX – I suspect it’s 32768 characters. You’ll hit this limit if you create a parameter on a field with thousands of values in and then select all but a few of these values; from the example above you can imagine how long the resulting DAX query could be:
There’s no single workaround for this but some custom DAX (such as the example here) will be necessary; you’ll just need to ensure that the DAX query is as short as possible.