A New Approach To Handling SSRS Multi-Valued Parameters in DAX Queries

Back in 2012 I wrote a blog post explaining how to handle multi-value parameters in DAX queries in Reporting Services reports. The approach I took back then was basically:

  1. Generate a string containing a pipe-delimited list of all the parameter values that the user has selected (I did this in DAX too, but to be honest it’s better to use the SSRS Join function to do this outside the DAX query)
  2. Use the DAX PathContains() function in a Filter() to check whether the value on the current row of the table being filtered appears in the pipe-delimited list

Here’s a deliberately simplified example of how this works based on Adventure Works DW data. The following query filters the FactInternetSales fact table and returns the rows for the Sales Order Numbers that are present in the OrderList variable:

VAR OrderList = "SO43713|SO43758|SO43784|SO43821"
    FILTER (
        PATHCONTAINS(OrderList, FactInternetSales[SalesOrderNumber])

The trouble with this approach is that is that it can be very slow. Running a trace in DAX Studio for the query above reveals the problem:


The presence of CallbackDataID shows that the Storage Engine is calling the Formula Engine to handle the use of PathContains() in the filter, and this is often a cause of poor query performance. However back when I wrote the post the only alternative was, as Chris Koester points out here, to dynamically generate the entire DAX query as an SSRS expression and that is very painful to do.

The good news is that recent changes in DAX mean that there is another way to tackle this problem that can give much better performance. Here’s an example of this new approach:

VAR OrderList = "SO43713|SO43758|SO43784|SO43821"
VAR OrderCount =
    PATHLENGTH ( OrderList )
VAR NumberTable =
    GENERATESERIES ( 1, OrderCount, 1 )
VAR OrderTable =
        VAR CurrentKey = [Value]
            ROW ( "Key", PATHITEM ( OrderList, CurrentKey ) )
VAR GetKeyColumn =
    SELECTCOLUMNS ( OrderTable, "Key", [Key] )
VAR FilterTable =
    TREATAS ( GetKeyColumn, FactInternetSales[SalesOrderNumber] )
    CALCULATETABLE ( FactInternetSales, FilterTable )

Broken down variable by variable, here’s how it works:

  1. OrderList is the pipe-delimited list of key values passed from SSRS
  2. OrderCount uses the PathLength() DAX function to find the number of parameter values in this list
  3. NumberTable uses the GenerateSeries() function to create a table of numbers with one row for each number between 1 and the number of parameter values in the list
  4. OrderTable uses the trick Marco describes here to iterate over NumberTable and, for each row, uses the PathItem() function to return one parameter value from the list for each row in the able
  5. GetKeyColumn uses the SelectColumns() DAX function to only return the column from OrderTable that contains the parameter values
  6. FilterTable uses the TreatAs() DAX function to take the table of values returned by GetKeyColumn and treat them as values in the FactInternetSales[SalesOrderNumber] column
  7. Finally, the query returns the contents of the FactInternetSales table filtered by the values in FilterTable using the CalculateTable() DAX function

There’s a lot of extra code here and in some cases you may find that performance with smaller data volumes is worse as a result, but in this particular case the new approach is twice as fast at the old one. There’s certainly no CallBackDataID:


10 responses

  1. Excellent Chris, was just looking for this from that session you did with us. It seems you had excellent timing with this blog post 🙂

  2. Pingback: Dew Drop - May 17, 2018 (#2727) - Morning Dew

  3. Pingback: Power BI Report Builder And RSCustomDaxFilter « Chris Webb's BI Blog

  4. Hi Chris, maybe you could write a fresh Blog about the difference between TREATAS and IN, when using DAX in SSRS.
    A performant solution for multiple optional multi-value-filters would be appreciated.

    At the moment I do it like this in my SSRS reports (connected to tabular SQL2019 instance), but the performance is not that great. When I replicate the same report in PowerBI it runs MUCH fast.
    I have about 25-30 parameters in some reports :/

    FILTER(VALUES(‘v_DimClaimPosition'[Decision]),PATHCONTAINS(substitute(substitute(substitute(@ParaClaimPosDecision, “{ “, “”), ” }”, “”), “,”, “|”), ‘v_DimClaimPosition'[Decision]) || @ParaClaimPosDecision = “”),
    FILTER(VALUES(‘v_DimBusinessPartner'[BusinessPartner]),PATHCONTAINS(substitute(substitute(substitute(@ParaBusinessPartner, “{ “, “”), ” }”, “”), “,”, “|”), ‘v_DimBusinessPartner'[BusinessPartner]) || @ParaBusinessPartner = “”),

  5. Hello, I am using DAX generated from PowerBI report and using it in Paginated report. When the parameter has multiple values, the query doesn’t understand it and returns blank. I see generated DAX query has parameter like that.

    Query generated by Power BI Performance Analyzer.

    VAR __DS0FilterTable2 =
    TREATAS({@GrowerGroup}, ‘Enrolled Growers'[Program_Group_Name])

    How do I change this TREATAS to accommodate multiple values?

    Query generated by Query Designer

    (‘Enrolled Growers'[Program_Group_Name], ‘POS'[Points],
    [Enrolled Growers].[Program_Group_Name],String))


Leave a Reply

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

%d bloggers like this: