SSRS Multi-Value parameter using DAX

SSRS Multi-Value parameter using DAX

In this post, we will how to set up multi-value parameters in SSRS using DAX.

Setting up Multi-Value Parameter using DAX in SSRS with 3 steps

For those who are already familiar with SSRS using MDX, it’ll be pretty straightforward as this is exactly the same logic, only the syntax changes and of course the language.

In this post, I’m using the AdventureWorks Tabular model database which you can download here

1. Multi-Value Parameter

We first need to create a parameter e.g “param_CountryRegionName” and then generate the country with the following DAX query:

capture
EVALUATE SUMMARIZECOLUMNS('Geography'[Country Region Name])

After running the above code in the DAX designer we can see the multiple values of the CountryRegionName parameter.

capture

2. Main Data Set

Once the parameter list is ready we need to create our main dataset.
We want to return the following:

  • Internet total sales
  • Margin
  • Result grouped by Product Category
  • Ability to filter by Country Region and by Fiscal Year
EVALUATE
SUMMARIZECOLUMNS (
'Product Category'[Product Category Name],
FILTER (
VALUES ( 'Geography'[Country Region Name] ),
PATHCONTAINS ( @CountryRegionName, 'Geography'[Country Region Name] )
),
FILTER (
VALUES ('Date'[Fiscal Year] ),
PATHCONTAINS ( @FiscalYear, 'Date'[Fiscal Year] )
),
"Internet Total Sales", [Internet Total Sales],
"Internet Total Margin", [Internet Total Margin]
)

Here the trick is to use PATHCONTAINS; in this example, PATHCONTAINS creates a string as a result of evaluating the parameter values and then checks for each country if they’re among the parameter values string.

3. Link the parameters to the main data set

capture

SSRS DAX multi-value parameters
=join(Parameters!CountryRegionName.Value,"|") 

Here the second trick is to use the SSRS function “Join” to concatenate the list of the selected parameters with a pipe.

Note: You must concatenate your parameter values with a pipe “|” as the Pathcontains function expects a pipe-delimited string. (Read more about the DAX PATHCONTAINS  and PATH functions)

capture

Leave a Reply

Your email address will not be published. Required fields are marked *