Cumulative data series displays error in a table in Power BI
I would like to display plan and fact cumulative data series in a dashboard with a bar and line combined chart and a table next to each other using Power BI Version: 2.59.5135.781 64-bit (2018. June) edition.
My DAX formula looks like this:
CUMULATIVE_FACT = CALCULATE(
SUM('FACT_TABLE'[FACT_VALUE]);
FILTER(
ALL('DATES');
'DATES'[YEAR]=MAX('DATES'[YEAR]) &&
'DATES'[DATE]<=MAX('DATES'[DATE])
)
)
Which works fine and gives a result as such (bars displayed as TÉNY refer to cumulative fact)
The cumulative plan (line referred to as TERV) series is identical to this but with plan figures. Also you can change the year so the aggregation only runs for the current year.
However, I would like to display either null (blank) or zero values for the fact series after a certain date which is given as a parameter. This parameter value is stored in a table with a single column and single row in a date type value.
So I modified my formula as such
CUMULATIVE_FACT = IF(VALUES('DATES'[DATE])<= MAX(PARAMETER_TABLE[PARAMETER_DATE]);
CALCULATE(
SUM('FACT_TABLE'[FACT_VALUE]);
FILTER(
ALL('DATES');
'DATES'[YEAR]=MAX('DATES'[YEAR]) &&
'DATES'[DATE]<=MAX('DATES'[DATE])
)
); 0)
The formula works fine for the chart but my table visual gives an error.
So the chart looks okay, perfectly the way I would like to display it, but the table gives back a 'A table of multiple values was supplied where a single value was expected' error message
Error message:
The column referred to in the message is basically the CUMULATIVE_FACT
measure, I just changed it for ease of understanding. I tried with BLANK()
instead of 0, but it looks the same.
No idea why it is not working with the table visual. Any ideas?
reporting-services powerbi dax dashboard cumulative-sum
add a comment |
I would like to display plan and fact cumulative data series in a dashboard with a bar and line combined chart and a table next to each other using Power BI Version: 2.59.5135.781 64-bit (2018. June) edition.
My DAX formula looks like this:
CUMULATIVE_FACT = CALCULATE(
SUM('FACT_TABLE'[FACT_VALUE]);
FILTER(
ALL('DATES');
'DATES'[YEAR]=MAX('DATES'[YEAR]) &&
'DATES'[DATE]<=MAX('DATES'[DATE])
)
)
Which works fine and gives a result as such (bars displayed as TÉNY refer to cumulative fact)
The cumulative plan (line referred to as TERV) series is identical to this but with plan figures. Also you can change the year so the aggregation only runs for the current year.
However, I would like to display either null (blank) or zero values for the fact series after a certain date which is given as a parameter. This parameter value is stored in a table with a single column and single row in a date type value.
So I modified my formula as such
CUMULATIVE_FACT = IF(VALUES('DATES'[DATE])<= MAX(PARAMETER_TABLE[PARAMETER_DATE]);
CALCULATE(
SUM('FACT_TABLE'[FACT_VALUE]);
FILTER(
ALL('DATES');
'DATES'[YEAR]=MAX('DATES'[YEAR]) &&
'DATES'[DATE]<=MAX('DATES'[DATE])
)
); 0)
The formula works fine for the chart but my table visual gives an error.
So the chart looks okay, perfectly the way I would like to display it, but the table gives back a 'A table of multiple values was supplied where a single value was expected' error message
Error message:
The column referred to in the message is basically the CUMULATIVE_FACT
measure, I just changed it for ease of understanding. I tried with BLANK()
instead of 0, but it looks the same.
No idea why it is not working with the table visual. Any ideas?
reporting-services powerbi dax dashboard cumulative-sum
add a comment |
I would like to display plan and fact cumulative data series in a dashboard with a bar and line combined chart and a table next to each other using Power BI Version: 2.59.5135.781 64-bit (2018. June) edition.
My DAX formula looks like this:
CUMULATIVE_FACT = CALCULATE(
SUM('FACT_TABLE'[FACT_VALUE]);
FILTER(
ALL('DATES');
'DATES'[YEAR]=MAX('DATES'[YEAR]) &&
'DATES'[DATE]<=MAX('DATES'[DATE])
)
)
Which works fine and gives a result as such (bars displayed as TÉNY refer to cumulative fact)
The cumulative plan (line referred to as TERV) series is identical to this but with plan figures. Also you can change the year so the aggregation only runs for the current year.
However, I would like to display either null (blank) or zero values for the fact series after a certain date which is given as a parameter. This parameter value is stored in a table with a single column and single row in a date type value.
So I modified my formula as such
CUMULATIVE_FACT = IF(VALUES('DATES'[DATE])<= MAX(PARAMETER_TABLE[PARAMETER_DATE]);
CALCULATE(
SUM('FACT_TABLE'[FACT_VALUE]);
FILTER(
ALL('DATES');
'DATES'[YEAR]=MAX('DATES'[YEAR]) &&
'DATES'[DATE]<=MAX('DATES'[DATE])
)
); 0)
The formula works fine for the chart but my table visual gives an error.
So the chart looks okay, perfectly the way I would like to display it, but the table gives back a 'A table of multiple values was supplied where a single value was expected' error message
Error message:
The column referred to in the message is basically the CUMULATIVE_FACT
measure, I just changed it for ease of understanding. I tried with BLANK()
instead of 0, but it looks the same.
No idea why it is not working with the table visual. Any ideas?
reporting-services powerbi dax dashboard cumulative-sum
I would like to display plan and fact cumulative data series in a dashboard with a bar and line combined chart and a table next to each other using Power BI Version: 2.59.5135.781 64-bit (2018. June) edition.
My DAX formula looks like this:
CUMULATIVE_FACT = CALCULATE(
SUM('FACT_TABLE'[FACT_VALUE]);
FILTER(
ALL('DATES');
'DATES'[YEAR]=MAX('DATES'[YEAR]) &&
'DATES'[DATE]<=MAX('DATES'[DATE])
)
)
Which works fine and gives a result as such (bars displayed as TÉNY refer to cumulative fact)
The cumulative plan (line referred to as TERV) series is identical to this but with plan figures. Also you can change the year so the aggregation only runs for the current year.
However, I would like to display either null (blank) or zero values for the fact series after a certain date which is given as a parameter. This parameter value is stored in a table with a single column and single row in a date type value.
So I modified my formula as such
CUMULATIVE_FACT = IF(VALUES('DATES'[DATE])<= MAX(PARAMETER_TABLE[PARAMETER_DATE]);
CALCULATE(
SUM('FACT_TABLE'[FACT_VALUE]);
FILTER(
ALL('DATES');
'DATES'[YEAR]=MAX('DATES'[YEAR]) &&
'DATES'[DATE]<=MAX('DATES'[DATE])
)
); 0)
The formula works fine for the chart but my table visual gives an error.
So the chart looks okay, perfectly the way I would like to display it, but the table gives back a 'A table of multiple values was supplied where a single value was expected' error message
Error message:
The column referred to in the message is basically the CUMULATIVE_FACT
measure, I just changed it for ease of understanding. I tried with BLANK()
instead of 0, but it looks the same.
No idea why it is not working with the table visual. Any ideas?
reporting-services powerbi dax dashboard cumulative-sum
reporting-services powerbi dax dashboard cumulative-sum
edited Nov 15 '18 at 16:26
Alexis Olson
13.4k21734
13.4k21734
asked Nov 15 '18 at 11:48
Garamvölgyi MihályGaramvölgyi Mihály
134
134
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
The problem is coming from this piece:
VALUES('DATES'[DATE])
This returns all values in the current filter context, not just a single one. That's why you're getting
A table of multiple values was supplied where a single value was expected
when you try to compare it to MAX(PARAMETER_TABLE[PARAMETER_DATE]
.
It works in the chart since VALUES('DATES'[DATE])
is always a single value that corresponds to the month on the axis, whereas the table has a total line that encompasses multiple months.
I think if you just turned off the total line, it would be OK. Otherwise, change VALUES('DATES'[DATE])
to an expression that returns a single date in the way you want. For example, MAX('DATES'[DATE])
might work.
Thank you very much, Alexis! Due to my rating I cannot upvote your answer, but it did work! I understand that in Total row the filter context allowed for multiple return values forDATES[DATE]
and this caused the problem. By removing the Totals the formula worked.
– Garamvölgyi Mihály
Nov 16 '18 at 9:34
FYI, you should be able to accept the answer even if you cannot vote on it.
– Alexis Olson
Nov 16 '18 at 14:54
THX - just did that :-)
– Garamvölgyi Mihály
Nov 19 '18 at 9:26
add a comment |
Your Answer
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%2f53318817%2fcumulative-data-series-displays-error-in-a-table-in-power-bi%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
The problem is coming from this piece:
VALUES('DATES'[DATE])
This returns all values in the current filter context, not just a single one. That's why you're getting
A table of multiple values was supplied where a single value was expected
when you try to compare it to MAX(PARAMETER_TABLE[PARAMETER_DATE]
.
It works in the chart since VALUES('DATES'[DATE])
is always a single value that corresponds to the month on the axis, whereas the table has a total line that encompasses multiple months.
I think if you just turned off the total line, it would be OK. Otherwise, change VALUES('DATES'[DATE])
to an expression that returns a single date in the way you want. For example, MAX('DATES'[DATE])
might work.
Thank you very much, Alexis! Due to my rating I cannot upvote your answer, but it did work! I understand that in Total row the filter context allowed for multiple return values forDATES[DATE]
and this caused the problem. By removing the Totals the formula worked.
– Garamvölgyi Mihály
Nov 16 '18 at 9:34
FYI, you should be able to accept the answer even if you cannot vote on it.
– Alexis Olson
Nov 16 '18 at 14:54
THX - just did that :-)
– Garamvölgyi Mihály
Nov 19 '18 at 9:26
add a comment |
The problem is coming from this piece:
VALUES('DATES'[DATE])
This returns all values in the current filter context, not just a single one. That's why you're getting
A table of multiple values was supplied where a single value was expected
when you try to compare it to MAX(PARAMETER_TABLE[PARAMETER_DATE]
.
It works in the chart since VALUES('DATES'[DATE])
is always a single value that corresponds to the month on the axis, whereas the table has a total line that encompasses multiple months.
I think if you just turned off the total line, it would be OK. Otherwise, change VALUES('DATES'[DATE])
to an expression that returns a single date in the way you want. For example, MAX('DATES'[DATE])
might work.
Thank you very much, Alexis! Due to my rating I cannot upvote your answer, but it did work! I understand that in Total row the filter context allowed for multiple return values forDATES[DATE]
and this caused the problem. By removing the Totals the formula worked.
– Garamvölgyi Mihály
Nov 16 '18 at 9:34
FYI, you should be able to accept the answer even if you cannot vote on it.
– Alexis Olson
Nov 16 '18 at 14:54
THX - just did that :-)
– Garamvölgyi Mihály
Nov 19 '18 at 9:26
add a comment |
The problem is coming from this piece:
VALUES('DATES'[DATE])
This returns all values in the current filter context, not just a single one. That's why you're getting
A table of multiple values was supplied where a single value was expected
when you try to compare it to MAX(PARAMETER_TABLE[PARAMETER_DATE]
.
It works in the chart since VALUES('DATES'[DATE])
is always a single value that corresponds to the month on the axis, whereas the table has a total line that encompasses multiple months.
I think if you just turned off the total line, it would be OK. Otherwise, change VALUES('DATES'[DATE])
to an expression that returns a single date in the way you want. For example, MAX('DATES'[DATE])
might work.
The problem is coming from this piece:
VALUES('DATES'[DATE])
This returns all values in the current filter context, not just a single one. That's why you're getting
A table of multiple values was supplied where a single value was expected
when you try to compare it to MAX(PARAMETER_TABLE[PARAMETER_DATE]
.
It works in the chart since VALUES('DATES'[DATE])
is always a single value that corresponds to the month on the axis, whereas the table has a total line that encompasses multiple months.
I think if you just turned off the total line, it would be OK. Otherwise, change VALUES('DATES'[DATE])
to an expression that returns a single date in the way you want. For example, MAX('DATES'[DATE])
might work.
answered Nov 15 '18 at 17:17
Alexis OlsonAlexis Olson
13.4k21734
13.4k21734
Thank you very much, Alexis! Due to my rating I cannot upvote your answer, but it did work! I understand that in Total row the filter context allowed for multiple return values forDATES[DATE]
and this caused the problem. By removing the Totals the formula worked.
– Garamvölgyi Mihály
Nov 16 '18 at 9:34
FYI, you should be able to accept the answer even if you cannot vote on it.
– Alexis Olson
Nov 16 '18 at 14:54
THX - just did that :-)
– Garamvölgyi Mihály
Nov 19 '18 at 9:26
add a comment |
Thank you very much, Alexis! Due to my rating I cannot upvote your answer, but it did work! I understand that in Total row the filter context allowed for multiple return values forDATES[DATE]
and this caused the problem. By removing the Totals the formula worked.
– Garamvölgyi Mihály
Nov 16 '18 at 9:34
FYI, you should be able to accept the answer even if you cannot vote on it.
– Alexis Olson
Nov 16 '18 at 14:54
THX - just did that :-)
– Garamvölgyi Mihály
Nov 19 '18 at 9:26
Thank you very much, Alexis! Due to my rating I cannot upvote your answer, but it did work! I understand that in Total row the filter context allowed for multiple return values for
DATES[DATE]
and this caused the problem. By removing the Totals the formula worked.– Garamvölgyi Mihály
Nov 16 '18 at 9:34
Thank you very much, Alexis! Due to my rating I cannot upvote your answer, but it did work! I understand that in Total row the filter context allowed for multiple return values for
DATES[DATE]
and this caused the problem. By removing the Totals the formula worked.– Garamvölgyi Mihály
Nov 16 '18 at 9:34
FYI, you should be able to accept the answer even if you cannot vote on it.
– Alexis Olson
Nov 16 '18 at 14:54
FYI, you should be able to accept the answer even if you cannot vote on it.
– Alexis Olson
Nov 16 '18 at 14:54
THX - just did that :-)
– Garamvölgyi Mihály
Nov 19 '18 at 9:26
THX - just did that :-)
– Garamvölgyi Mihály
Nov 19 '18 at 9:26
add a comment |
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%2f53318817%2fcumulative-data-series-displays-error-in-a-table-in-power-bi%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