SSRS Multivalue Parameters in DAX

UPDATE: Microsoft has added support for multi-valued parameters in the DAX Query Designer. Please read the discussion thread below for more info. You can use the approach discussed in this article if you need more control over the parameter handling, but the Microsoft-provided way should suffice for most cases and it’s easier to implement.

Déjà vu today with a twist. SSRS multivalue parameters in SSAS reports but this time in DAX. Now that SSAS Designer supports DAX queries, we should be able to do everything we were able to do in MDX, right? Unfortunately, as you will quickly discover, Microsoft “forgot” about multivalue parameters when working on the DAX Designer.

You can use MDX (no shame there) and write queries the old way, but if you are a DAX purist, you’d need to take the road less traveled which goes through the DAX rabbit hole.

Here are the high-level steps in the SSDT Report Designer/Report Builder and I tried my best to simplify this as much as I can:

1. Do as much drag and drop using the DAX Graphical Query Designer to auto-generate the DAX query, as you won’t have another chance once you switch to a text mode. You can also use the graphical mode to declare your parameter(s) and to let SSRS autogenerate the report-level parameters and queries.
2. If you let the DAX Query Designer auto-generate the parameter queries, change the Available Values of the report-level parameters to use ParameterCaption field (not the ParameterValue column). If the parameter uses default values, change the Default Values tab to set the default values by captions (not using pipe-delimited format that the DAX Designer auto-generates). Again, that’s because we’d use the parameter caption.
   
3. Go to the properties of the main dataset, flip to the Parameters tab and change the expression to concatenate the parameter values with a pipe “|”, such as =Join(Parameters!DateFiscalYear.Value,”|”). You’ll see why in a moment.
   
4. Now open the main DAX report query and switch to Text mode. Promise yourself never to go back to the Graphical mode (the one that lets you drag and drop). Click the Parameters button and reconfigure the parameter by selecting the empty value in the Dimension column. For testing the query inside the query designer, you might want to enter some pipe-delimited values in the Default column.
   
5. Change the main query to support multivalue parameters. The following query highlights the important changes:

   EVALUATE
   SUMMARIZECOLUMNS (
   ‘Date'[Fiscal Year],
   FILTER (
   VALUES ( ‘Date'[Fiscal Year] ),
   (
               OR (
                   ( @DateFiscalYear = “All” ),
                   PATHCONTAINS ( @DateFiscalYear, ‘Date'[Fiscal Year] )
               )
   )
   ),
   “Internet Total Sales”, [Internet Total Sales],
   “Reseller Total Sales”, [Reseller Total Sales]
   )

I removed the variable (VAR) definitions (not needed after simplifying the query). The filter expression uses an OR condition. If the user selects the parameter “All” value, then all rows are returned. If specific values are selected, the PATHCONTAINS function would return TRUE for that row in the filtered column (‘Date'[Fiscal Year] in this case) that matches one of the selected values. If you have more parameters, simply add more FILTER clauses.

I attach a report to demonstrate the changes.