SSRS Multi-Value parameter in DAX


Setting up Multi-Value Parameter using DAX in 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…

You can download the  AdventureWorks Tabular Database Sample here

1. Multi-Value Parameter

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

capture

 EVALUATE SUMMARIZECOLUMNS('Geography'[Country Region Na
me])

We can run the above code in the DAX designer to 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 Internet total sales as well as the margin grouped by Product Category with the 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

capture

 =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 *