There are several articles (such as this one from Chris) about how to solve multivalued parameters problem when you call DAX from SSRS. It seems to be great and simple when testing, but can hurt you when you try to pass many values and not just a few. Chris has as well note about potential issue and I have real data from my usage.
I have used this method and I faced performance degradation when consumers passed more than 10-20 values. So I decided to simply test two approaches to the same query using PATHCONTAINS and another one simply using OR.
First query:
EVALUATE ( CALCULATETABLE ( ADDCOLUMNS ( CROSSJOIN ( FILTER ( ‘YearAverages’, ‘ YearAverages ‘[YearCnt] <> 0 ), FILTER ( ALL ( Country[Country Name] ), PATHCONTAINS ( “Germany|Sweden|Austria|Poland|Slovakia|Slovania|France”, Country[Country Name] ) ) ), “Lost Ratio Avg in Years”, [Lost Ratio Avg in Years],“Lost Ratio Median in Years”, [Lost Ratio Median in Years]), ‘Target Currency'[Target Currency v] = “USD” ) ) ORDER BY Country[Country Name], ‘ YearAverages ‘[YearCnt] |
Uses the PATHCONTAINS and runs 1.3 sec.
Second query uses OR and runs 0.4 seconds.
EVALUATE ( CALCULATETABLE ( ADDCOLUMNS ( CROSSJOIN ( FILTER ( ‘YearAverages’, ‘ YearAverages ‘[YearCnt] <> 0 ), FILTER ( ALL ( Country[Country Name] ), Country[Country Name] = “Germany” || Country[Country Name] = “Sweden” || Country[Country Name] = “Austria” || Country[Country Name] = “Poland” || Country[Country Name] = “Slovakia” || Country[Country Name] = “Slovenia” || Country[Country Name] = “France” ) ), “Lost Ratio Avg in Years”, [Lost Ratio Avg in Years], “Lost Ratio Median in Years”, [Lost Ratio Median in Years] ), ‘Target Currency'[Target Currency v] = “USD” ) ) ORDER BY Country[Country Name], ‘ YearAverages ‘[YearCnt] |
You can see more detailed result in following table and chart.
Value Count | Duration Using PATHCONTAIN (ms) | Duration Using OR (ms) |
1 |
515 |
374 |
5 |
1066 |
386 |
10 |
1519 |
429 |
20 |
2641 |
462 |
As you can see on chart – with query using OR the performance is increasing just a little bit. There is small performance difference when filtering on 1 countries or 40. On the other hand, query which uses PATHCONTAINS for filtering slows down linearly with count of countries I pass into parameter.
Therefore I would recommend using a different practice for handling multivalued parameters in SSRS which is basically using dynamic query such as in this blog.