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:
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
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
Here the second trick is to use the SSRS function “Join” to concatenate the list of the selected parameters with a pipe.