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:
- 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.
- 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.
- 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.
- 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.
-
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.
This post was very helpful! Thanks for sharing! I have set up a report using your method and I have multiple parameters. So as you suggested at the end of your post, I added more filter clauses. This is my DAX query for the report:
EVALUATE
SUMMARIZECOLUMNS(
‘ApptCountDistribution'[PatientId],
FILTER(
VALUES(‘ApptCountDistribution'[DailySummaryFacility])
,(
OR (
(@ApptCountDistributionDailySummaryFacility = “All”),
PATHCONTAINS (@ApptCountDistributionDailySummaryFacility, ‘ApptCountDistribution'[DailySummaryFacility])
)
)
),
FILTER(
VALUES(‘ApptCountDistribution'[Specialty])
,(
OR (
(@ApptCountDistributionSpecialty = “All”),
PATHCONTAINS (@ApptCountDistributionSpecialty, ‘ApptCountDistribution'[Specialty])
)
)
),
FILTER(
VALUES(‘ApptCountDistribution'[ResponsibleProvider])
,(
OR (
(@ApptCountDistributionResponsibleProvider = “All”),
PATHCONTAINS (@ApptCountDistributionResponsibleProvider,
‘ApptCountDistribution'[ResponsibleProvider])
)
)
),
FILTER(
SUMMARIZE(
‘ApptCountDistribution’
,’ApptCountDistribution'[ApptDate]
),
‘ApptCountDistribution'[ApptDate] >= DATEVALUE(@FromApptCountDistributionApptDate)
&& ‘ApptCountDistribution'[ApptDate] <= DATEVALUE(@ToApptCountDistributionApptDate) ), "ApptDistributionApptCount" , [ApptDistributionApptCount] ) Problem I am having is that when I run the report, the parameters act as cascading parameters. Which is fine, but if I select multiple values in the first parameter it will only show All in the second parameter. Am I missing a step in the process? I have attached to images one that show selecting multiple values in the first parameter called DailySummaryFacility. And the second show that after selecting multiple in the first, All is the only option in the Specialty parameter. If I select one value in the first parameter everything works fine, other https://uploads.disquscdn.com/images/a56a5f65120d1c54120a3d382d37cacf08ade7d17e51bf6fd28c81300d290882.png https://uploads.disquscdn.com/images/9aa9463e58e0438891944f0efc89772fded7f394e528c4e680e8f7c90d6f9382.png
Hi Prologika,
I am using Power BI dataset as my data source and tried implementing the same logic you have mentioned above, but it is giving me an error(Query preparation failed).
Regards,
Uday
how do you do it for multiple and cascading parameters? Can you share?
Hello.
I recently updated my software to v.15.0.19914.0
Doing so seems to have altered the XML to all of my reports, in addition to impeding my multi-value parameters. I can no longer default to “All” parameter choices also as indicated by user “Lina”. I have tried:
1. Enabled/disabled Multi value parameters
2. Implemented, removed the syntax in the described PATHCONTAINS solution.
3. Removed, recreated each of my parameters within the query, and with the “Report Data>Parameters” section of Report Builder.
None of which has resolved this issue. Some others in my group have a similar experience. Others do not. I am trying to ascertain if it is a configuration issue with some of our machine builds, or something related to Report builder.
Any suggestions are most welcome!
Old post but hopefully still monitored.
I’m using DAX code imported from a visual in Power BI and so my filter code looks slightly different.
How should I edit the following to allow it use a joined multi value parameter?
VAR __DS0FilterTable2 =
TREATAS({@SiteDescription}, ‘FactAction'[Site Description])