Cumulative data series displays error in a table in Power BI












2















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.



Results of the first formula



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.



Results of the second forrmula



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:



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?










share|improve this question





























    2















    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.



    Results of the first formula



    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.



    Results of the second forrmula



    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:



    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?










    share|improve this question



























      2












      2








      2








      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.



      Results of the first formula



      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.



      Results of the second forrmula



      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:



      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?










      share|improve this question
















      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.



      Results of the first formula



      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.



      Results of the second forrmula



      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:



      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






      share|improve this question















      share|improve this question













      share|improve this question




      share|improve this question








      edited Nov 15 '18 at 16:26









      Alexis Olson

      14.6k21834




      14.6k21834










      asked Nov 15 '18 at 11:48









      Garamvölgyi MihályGaramvölgyi Mihály

      134




      134
























          1 Answer
          1






          active

          oldest

          votes


















          0














          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.






          share|improve this answer
























          • 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











          • THX - just did that :-)

            – Garamvölgyi Mihály
            Nov 19 '18 at 9:26











          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
          });


          }
          });














          draft saved

          draft discarded


















          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









          0














          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.






          share|improve this answer
























          • 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











          • THX - just did that :-)

            – Garamvölgyi Mihály
            Nov 19 '18 at 9:26
















          0














          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.






          share|improve this answer
























          • 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











          • THX - just did that :-)

            – Garamvölgyi Mihály
            Nov 19 '18 at 9:26














          0












          0








          0







          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.






          share|improve this answer













          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.







          share|improve this answer












          share|improve this answer



          share|improve this answer










          answered Nov 15 '18 at 17:17









          Alexis OlsonAlexis Olson

          14.6k21834




          14.6k21834













          • 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











          • 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











          • 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




















          draft saved

          draft discarded




















































          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.




          draft saved


          draft discarded














          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





















































          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







          Popular posts from this blog

          Bressuire

          Vorschmack

          Quarantine