Powerpivot VALUES function

[Erwin]

Hi all,

I'm trying to recreate a PBI Desktop dashboard in Excel 2013 Powerpivot as part of a case study into different tooling options for my company. Below you will find a example of my data.

In PBI desktop I have created the following formulas:

Running total = CALCULATE(SUM('Table'[Amount]),FILTER(ALL('Calendar'[Date]),'Calendar'[Date]<=MAX('Calendar'[Date])))

Amounts invoiced = CALCULATE([Running total],'Table'[Doc type]="Invoice")

Invoices cleared = CALCULATE([Amounts invoiced],'Table'[Clearing date]<=VALUES(Calendar[Date]),'Table'[Clearing date]<>0)

In Powerpivot the first two formulas work and give the same result as in PBI desktop. The third formula doesn't work and gives the following error message when creating a pivottable:

"MdxScript(Model) (6,95) Calculation error in measure 'Table'[Invoices cleared]: A table of multiple values was supplied where a single value was expected."

I've narrowed it down to the use of the VALUES function. This one doesn't seem to work in Powerpivot (for this scenario at least) where it does work in PBI desktop.

My questions are:

1. Is the issue I describe a know issue in Powerpivot?
2. Is there a known workaround available?

Many thanks in advance for your input.

Rg. Erwin

[Oxenskiold]

Hi Erwin,

There is one situation in which your measure  CALCULATE([Amounts invoiced],'Table'[Clearing date]<=VALUES(Calendar[Date]),'Table'[Clearing date]<>0) will not  generate an error while used in Power BI. If you filter down the ' Calendar[Date]' field to one value. You can do this through a slicer or a page- or visual filter. Is this the case in your Power BI dashboard?  That would explain why an error is not generated in POWER BI but occurs in Power Pivot (Until you filter down here too).

What @mattbrice writes in his answer is a superb explanation (and solution) of the whole matter.

The ( 'Table'[Clearing date] <> 0 ) filter argument is just syntax sugar for the FILTER(ALL(Table[column), <Boolean expression>)  construction (as explained in some of the other answers) and is called a well-formed-boolean  expression.

It could be written in any of the following ways too:

NOT ( ISBLANK ( 'Table'[Clearing date] ) )
 
'Table'[Clearing date] <> BLANK ()
 
'Table'[Clearing date]
 

 The last example 'Table'[Clearing date]  will probably surprise many however the DAX engine will interpret  the expression as 'Table'[Clearing date]  not being equal to zero (because a date is represented by a floating point number internally and zero represents false).

Best regards Oxenskiold

[v-huizhn-msft]

Hi @Erwin,

All the three formula works fine in Power BI desktop? And there is a relationship between your actual table and Calendar table? For the filter “'Table'[Clearing date]<=VALUES(Calendar[Date])” in your formula. VALUES(Calendar[Date]) return one value, while 'Table'[Clearing date] have many values, it doesn't make sense.

In addition, 'Table'[Clearing date]<>0 return "True" or "False" resulat, you should put it into filter functions like the first two formulas.

Could you please share more details for further analysis?

Best Regards,
Angelia

[Greg_Deckler]

Are you sure that it is the VALUES function and not the comparison operation with Table[Clearing date] that is the real source of the issue?

[Erwin]

@Greg_Deckler

No, it's definitely the VALUES function. If I take that out the remainder of the function works, it just doesn't give me the result I want. The comparison with Table[Clearing date] works as expected.

@v-huizhn-msft

The VALUES function should return a one-column table that contains the distinct values from the specified table or column. So in this case I would expect a one-column table of dates from Calendar[Date]. Table[Doc date] and Calendar[Date] have a relationship.

Rg. Erwin

[mattbrice]

The problem is just what the error message states.   You are trying to compare multiple values which is not allowed.  Your code is:

CALCULATE (
    [Amounts invoiced],
    'Table'[Clearing date] <= VALUES ( Calendar[Date] ),
    'Table'[Clearing date] <> 0
)

which is just Dax shorthand for

CALCULATE (
    [Amounts invoiced],
    FILTER (
        ALL ( 'Table'[Clearing date] ),
        'Table'[Clearing date] <= VALUES ( Calendar[Date] )
    ),
    FILTER ( ALL ( 'Table'[Clearing date] ), 'Table'[Clearing date] <> 0 )
)

FILTER is an iterator that makes a row by row comparison but only against a single value.  VALUES returns a single column table of all unique values (including 'Blank' value if applicable) visible in the current filter context.  So the way you are using it, the DAX engine is erroring out on the possibility your VALUES call will return multiple row values and therefore make the comparison ambiguous and unresolvable.   VALUES will convert a single column, single row result into a scalar value but to do so we usually protect the function by wrapping it in an "IF( HASONEVALUE ( Calendar[Date] ), .... ) " call.  Or if relevant, change the "VALUES" to "MAX" or "MIN".  Give it a try to see if the errors stop (and if you are getting value you expect).

The "'Table'[Clearing date] <> 0"  row comparison is against a single scalar value and so is an allowable function call and is not the cause of your problem.  Hope this helps....

[Erwin]

@mattbrice

OK, thanks for the explanation, I see your point. So in PowerPivot the function as I have written it will work with one date selected in the report, but not when multiple dates have been selected. This is due to the use of the VALUES function.

Then my question is: "How would you write this function when you want to display results for multiple dates (e.g. in a line chart displaying values for the last month)?"

Because this is where PBI desktop and PowerPivot differ. Is use the exact same function in a line chart with multiple dates in PBI desktop without getting any error messages.

Hope to hear from you!

Rg. Erwin

[mattbrice]

That would depend on how you have your table structured and what you have on the rows/columns/filter areas of the pivot and what you want your end result to look like.

If you are putting indiviudal days in the pivot chart, then shouldn't need a CALCULATE statement at all.  Just add the [Amounts Invoiced] measure with the row/column date filters doing their normal thing.

[Greg_Deckler]

Hence, it is the comparison operation that you are trying to perform with a function that potentially brings back multiple values which is causing your problem.