Power BI Report Builder And RSCustomDaxFilter

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:

image

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:

image

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:

image

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.

7 responses

  1. 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.

  2. 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,

  3. 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.

  4. 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]
      )

  5. 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 🙁

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: