This post shows how you can generate optimized multi-value DAX parameters in SSRS and achieve greater performance compared to the DAX PathContains function. This will be a short post that provides the SSRS expression to convert multiple SSRS parameters into a double-pipe delimited string for use in a DAX query. In other words, the goal is to use the DAX OR operator (||) instead of the PathContains function. I’m assuming the reader has experience with SSRS, so not all steps will be shown.
Imagine that you have multi-value parameter that allows users to select multiple month - year values such as Sep 2017, Oct 2017, etc. The easiest way to use these parameters in a DAX query is with the PathContains function:
EVALUATEFILTER ( ‘Calendar’, PATHCONTAINS ( “Sep 2017|Oct 2017”, ‘Calendar’[Month – Year] ))
To use an SSRS parameter in this query, you would substitute the first argument of PathContains with a parameter containing this expression for the value:
=JOIN(Parameters!MonthYear.Value, "|")
As Chris Webb mentions in his post, Handling SSRS Multi-Valued Parameters in DAX Queries, the PathContains function is not very efficient. Using the OR operator (||) as shown below is much more efficient:
EVALUATEFILTER ( ‘Calendar’, ‘Calendar’[Month – Year] = “Sep 2017” || ‘Calendar’[Month – Year] = “Oct 2017”)
Using an SSRS parameter in this query requires a more complicated expression:
="Calendar[Month - Year] = """ & REPLACE(JOIN(Parameters!MonthYear.Value, ","), ",", """ || Calendar[Month - Year] = """) & """"
If you’ve worked with multi-value parameters with SSRS and MDX, you will recognize this pattern. If you put this expression in a text box in SSRS it will evaluate to this:
Calendar[Month - Year] = "Sep 2017" || Calendar[Month - Year] = "Oct 2017"
To use this pattern, you actually need to generate the entire DAX query using an expression. I’ve added carriage-return/linefeed characters (vbcrlf) to make the results easier to read when evaluated in a text box. You can leave them there for the query expression as well, but they do clutter the code.
="EVALUATE" & vbcrlf &
"FILTER ('Calendar'," & vbcrlf &
"Calendar[Month - Year] = """ & REPLACE(JOIN(Parameters!MonthYear.Value, ","), ",", """ || Calendar[Month - Year] = """) & """" & vbcrlf &
")"
I tested this technique with a query against a real-world table containing 1.8 million rows. The query using PathContains took about 500 milliseconds, and the query that used the OR operator took about 30 milliseconds (16 times faster!). With sub-second performance, the PathContains query doesn’t really require optimization, but with larger tables or more complicated queries, this optimization could be worthwhile and noticeable to report users.
The advantage to this approach is ludicrous speed with your DAX-based SSRS reports. The disadvantage is that you have to use an expression to generate the DAX query, which can make SSRS development more cumbersome.