Search code examples
reporting-servicespowerbidaxdashboardcumulative-sum

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.

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?

Solution

  • 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.