SELECT All in SSRS Parameters using DAX language

Today let’s talk about how to add Select All in parameters using DAX language. Recently we have a requirement where we need to add a “Select All” value apart from other values we were getting from cube using DAX language. This kind of ask is very common in SSRS reports. In SQL Server we can achieve this task very easily using Union All operator but in DAX it is quite difficult. So in our case a simple scalable solution came from Isha Mattoo. Before we jump on the solution directly let’s check out the example below-

Now before doing union all operation lets first write a DAX query to get for account number. Check out the query below-

--

Evaluate
(
	CalculateTable
	(
		 Summarize ( InternetSales, Accounts[AccountNumber] ),
         Dates[Month] >= DateValue ( "2013-07-01 00:00:00" ),
		 Dates[Month] <= DateValue ( "2015-06-01 00:00:00" )
        )
)
ORDER BY [AccountNumber] DESC

--

In the above query we are fetching accounts for a particular period. Summarize will give us distinct account numbers and Calculate table is used to filter out values as where clause in SQL Server. Calculate table returns a data table.

Now lets add a Select All in the above query

--

Evaluate
(
	Summarize
	(
			 (
				AddColumns (
							 ( 

									CalculateTable
									(
										 Summarize ( InternetSales, ROLLUP ( Accounts[AccountNumber] ) ),
										 Dates[Month] >= DateValue ( "2013-07-01 00:00:00" ),
										 Dates[Month] <= DateValue ( "2015-06-01 00:00:00" )
								   )
							 ),
							"AccNumber", If (ISBlank ( [AccountNumber] ),  "Select All",  [AccountNumber] )
							)
				),
                [AccNumber]
   )
)
ORDER BY [AccNumber] DESC

--

Now lets check out the output

So the inner part of the query is very easy we are finding distinct account number with date filters using calculate table. In the outer part we have added all in the account numbers column and then we have a rollup in the inner section which will club the things together. After that we were just picking the alias [AccNumber]. DAX is bit difficult to understand but once you work with closely, you can easily win it.

I hope you have enjoyed the article. Thanks for reading !

-Pawan Khowal

MSBISkills.com