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:
- 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)
- 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:
[sourcecode language=’text’ padlinenumbers=’true’]
EVALUATE
VAR OrderList = “SO43713|SO43758|SO43784|SO43821”
RETURN
FILTER (
FactInternetSales,
PATHCONTAINS(OrderList, FactInternetSales[SalesOrderNumber])
)
[/sourcecode]
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:
[sourcecode language=’text’ ]
EVALUATE
VAR OrderList = “SO43713|SO43758|SO43784|SO43821”
VAR OrderCount =
PATHLENGTH ( OrderList )
VAR NumberTable =
GENERATESERIES ( 1, OrderCount, 1 )
VAR OrderTable =
GENERATE (
NumberTable,
VAR CurrentKey = [Value]
RETURN
ROW ( “Key”, PATHITEM ( OrderList, CurrentKey ) )
)
VAR GetKeyColumn =
SELECTCOLUMNS ( OrderTable, “Key”, [Key] )
VAR FilterTable =
TREATAS ( GetKeyColumn, FactInternetSales[SalesOrderNumber] )
RETURN
CALCULATETABLE ( FactInternetSales, FilterTable )
[/sourcecode]
Broken down variable by variable, here’s how it works:
- OrderList is the pipe-delimited list of key values passed from SSRS
- OrderCount uses the PathLength() DAX function to find the number of parameter values in this list
- 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
- 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
- GetKeyColumn uses the SelectColumns() DAX function to only return the column from OrderTable that contains the parameter values
- 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
- 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:
Excellent Chris, was just looking for this from that session you did with us. It seems you had excellent timing with this blog post 🙂
amazing!!! thank you
brilliant post, saved me writing dynamic code!
Why not we try “IN” function for this scenario chris…
I wrote this post before the IN operator was available
Hi Chris! In case you use “IN” operator for this scenario, how, would you do that?
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 = “”),
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
EVALUATE SUMMARIZECOLUMNS
(‘Enrolled Growers'[Program_Group_Name], ‘POS'[Points],
RSCustomDaxFilter(@GrowerGroup,EqualToCondition,
[Enrolled Growers].[Program_Group_Name],String))
Thanks,
Ritesh
Ritesh,
I am in similar situation. Did you find solution to your problem?
Hi Ritesh,
I have the same requirements, I’m using DAX Query from Performance Analyzer. May I know if you found a solution to your problem?
Does anyone have the answer for:
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.
Need to replace TREATAS with RSCustomDaxFilter ?
Does anyone have sample code for that conversion?
Thanks