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:
EVALUATE
SUMMARIZECOLUMNS(
RSCustomDaxFilter(
@DateCalendarYear,
EqualToCondition,
[Date].[Calendar Year],
Int64
),
"Internet Total Sales",
[Internet Total Sales]
)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):
EVALUATE
SUMMARIZECOLUMNS (
FILTER (
VALUES ( 'Date'[Calendar Year] ),
( 'Date'[Calendar Year] = VALUE ( "2013" ) )
|| ( 'Date'[Calendar Year] = VALUE ( "2014" ) )
),
"Internet Total Sales", [Internet Total Sales]
)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.
Hi Chris,
Any idea on you can make one of these DAX multi-select parameters optional? They are always showing as Required in my reports which results in long DAX queries (for Select All) when really I want to avoid setting the parameter if filter is not needed in some instances.
Hi Chris,
It seems RSCustomDaxFilter doesn’t work with the new version of Power BI Report Builder (v15.6.01207.0002-1303e236).
I have an error message when I try to execute the DAX query and when I click OK in the Query Designer.
Regards,
Matthew, try removing any carriage returns, tabs from your DAX query. That did the trick for me. For some reason RSCustomDaxFilter fails when the carry the parameters across lines.
Get this error, Product is a parameter with multi value
the dataset runs fine inside query designer, any idea? I have latest report builder as of 9/26/2020
EVALUATE
SUMMARIZECOLUMNS( ‘DimProduct'[EnglishProductName], ‘DimDate'[CalendarYear], “InetSales”, ‘FactInternetSales'[InetSales], RSCustomDaxFilter(@Product,EqualToCondition,[DimProduct].[EnglishProductName],String))
Query (2, 124) Function SUMMARIZECOLUMNS expects a column name as argument number 5.
—————————-
Query execution failed for dataset ‘Sales’.
—————————-
An error has occurred during report processing.
—————————-
An error occurred during local report processing.
You should move “InetSales” after RSCustomDaxFilter. SUMMARIZECOLUMNS take at first “group by” , then all filters, then all calculate and measures.
EVALUATE
SUMMARIZECOLUMNS( ‘DimProduct'[EnglishProductName], ‘DimDate'[CalendarYear], RSCustomDaxFilter(@Product,EqualToCondition,[DimProduct].[EnglishProductName],String)
,“InetSales”, ‘FactInternetSales'[InetSales]
)
https://powerbi.microsoft.com/en-us/blog/power-bi-paginated-reports-august-2020-updates/
Improved multi-value parameters support for DAX
When building DAX queries for a report with multi-value parameter from a Power BI dataset, users often would run into issues if they had too many parameter values and bump up against the maximum length of a query.
RSCustomDaxFilter expands each value before the DAX query is executed
With this release, we’ve changed how we handle this in product and most users should no longer see this error when using Power BI Datasets or Azure Analysis Services as their data source.
BUT no info about SSRS 🙁
Waiting for fix in SSRS too 🙁
A problem is that the Parameter is not optional. I just tested RSCustomDaxFilter() and when I do not enter a value in the Parameter, the runtime of the report is the same as when I enter a value, but the result is 0 rows.
The only way to implement Parameters that can be made optional and allows single or multi value input into a SSRS-Parameter (connected to a tabular model) seems to be the PATHCONTAINS-way, sadly it has quite slow performance.
Hello Chris,
If I have to choose between using SQL vs DAX for creating Power BI paginated reports, which one should I choose? Is SQL a better choice for multi valued parameters?
Hello Chris, can i ask for an update on the article where it stresses the importance of using the right syntax on the RSCustomDAXFilter? i have just spent a large amount of time trying to find the cause of a failure when using the parameter method. It turned out that I had put a space before the column reference in the syntax. It turns out that the “, [table].[Column]” was interpreted as [ table].[column]. The error message I received in Report Builder was Parameter not declared, which was misleading for my understanding.
I’m guessing other cases can be important to understand when using the RSCustomDAXFilter.
Thanks for the great content.
Hi. Is there a way to modify the generated DAX query such that it can filter the results to bring back 13 months of information, ending on the date that is selected in the parameter dropdown? like possibly saying something like:
RSCustomDaxFilter(
@DateCalendarYear,
GreaterThanOrEqualToCondition,
[Date].[Calendar Year] -13 Months and @DateCalendarYear LessThanOrEqualToCondition,[Date].[Calendar Year]