Forum Discussion
Help needed to conditionally assign a table variable in DAX (from SSRS dataset)
Two quick things first.. One, I'm very new to DAX. Two, I'm using DAX in an SSRS dataset query (i.e. not in Power BI), so please let me know if there is a different forum where I should post this question. Thanks!
My current 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:
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 new 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!
Are you using GroupIDs_Selected as a filter to your main query? If that is the case a simpler approach is to just use FILTER and considering only GroupLabel. Since, DAX maintains lineage, you don't need to have the ID column for filter to work.
DEFINE VAR GroupIDs_Selected = FILTER ( VALUES ( 'Groups'[GroupLabel] ), 'Groups'[GroupLabel] = userInput || userInput = "All" )
2 Replies
- AkhilAshok
Solution Sage
Are you using GroupIDs_Selected as a filter to your main query? If that is the case a simpler approach is to just use FILTER and considering only GroupLabel. Since, DAX maintains lineage, you don't need to have the ID column for filter to work.
DEFINE VAR GroupIDs_Selected = FILTER ( VALUES ( 'Groups'[GroupLabel] ), 'Groups'[GroupLabel] = userInput || userInput = "All" )- AnonymousNot applicable
^ Thanks! That was the exact guidance that I needed!