Conditionally assign a table variable in a DAX query (within an SSRS dataset)
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!
reporting-services ssas dax
asked Nov 21 '18 at 17:43
RobLRobL
12
add a comment |
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!
reporting-services ssas dax
asked Nov 21 '18 at 17:43
RobLRobL
12
add a comment |
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!
reporting-services ssas dax
asked Nov 21 '18 at 17:43
RobLRobL
12
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!
reporting-services ssas dax
reporting-services ssas dax
asked Nov 21 '18 at 17:43
RobLRobL
12
asked Nov 21 '18 at 17:43
RobLRobL
12
asked Nov 21 '18 at 17:43
RobLRobL
12
asked Nov 21 '18 at 17:43
RobLRobL
12
asked Nov 21 '18 at 17:43
RobLRobL
12
12
add a comment |
add a comment |
0
active
oldest
votes
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53417820%2fconditionally-assign-a-table-variable-in-a-dax-query-within-an-ssrs-dataset%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53417820%2fconditionally-assign-a-table-variable-in-a-dax-query-within-an-ssrs-dataset%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
