Conditionally assign a table variable in a DAX query (within an SSRS dataset)

Question

I'm working on an SSRS dataset, trying to write a DAX query that can filter a table in a tabular model based on user input. The specific challenge is that I am trying to conditionally create a DAX table variable based on input from a user.

Simplified example: Say I have a table in my tabular model called "Groups" with two columns "GroupID" and "GroupLabel". Based on input from the user, I need to create a table variable (something akin to a SQL temp table) that contains the GroupID's from the "Groups" table that correspond with the user-input "GroupLabel". I have successfully accomplished this via the following:

DEFINE
VAR GroupIDs_Selected = 
SUMMARIZE(
CALCULATETABLE(
'Groups', 
'Groups'[GroupLabel] = userInput
),
'Group'[GroupID]
)

However we also have an option that the user can select for "All" groups. So in order to accomplish this, the table variable would need to look something like this:

DEFINE
VAR GroupIDs_Selected =
SUMMARIZE('Groups', 'Groups'[GroupID])

The above two variable definitions appear to work fine separately, but my problem is that I haven't figured out a way to combine these two variable definitions conditionally. For example, the following definition which attempts to combine the above two definitions inside an IF function is not working for me:

DEFINE
VAR GroupIDs_Selected = 
IF( userInput = "All", 
SUMMARIZE('Groups', 'Groups'[GroupID]),
SUMMARIZE(
CALCULATETABLE(
'Groups', 
'Groups'[GroupLabel] = userInput
),
'Group'[GroupID]
)

^ More specifically, when I try to reference this conditional variable as a table (for example in an EVALUATE statement), I get the following error message: "The expression specified in the query is not a valid table expression".

My question.. Is there something that I'm missing inside the IF statement? Maybe I have to include a function to somehow cast the conditionally returned value as a table? Or is this something that can't be done within a DAX table variable assignment statement? Or is there another completely different way to handle this kind of scenario within a tabular model?

Any help would be greatly appreciated!

Geraldziu Geraldziu · Accepted Answer · 2019-04-30T08:14:46

It depends on what kind of parameter do you want to use. If it's simple one value text parameter then use a simple filter within SUMMARIZE like this

SSRS:

par1 available values (A|B|C|D); users choice A

DAX:

EVALUATE(
SUMMARIZECOLUMS(
'Item'[Category]
FILTER('Item', 'Item'[Group] = @ssrsParam)
"sales" [value of items]
)

If the parameter is multivalue you can do something like this

SSRS:

par1 available values (A|B|C|D); users choice A and B

in dataset use the par1 as a formula and concatenate value's using Join

=Join(Parameters!ReportParameter1.Value, "|")

DAX:

EVALUATE(
SUMMARIZECOLUMS(
'Item'[Category]
FILTER(VALUES('Item'[Group]), PATHCONTAINS(@ssrsParam, 'Item'[Group]))
"sales" [value of items]
)

Conditionally assign a table variable in a DAX query (within an SSRS dataset)

1 Answers