Using FILTER with VALUES (multiple rows error)

Question

I am learning DAX on the Contoso database provided for The Definitive Guide to DAX book.

I am trying to understand filter context and I wrote this measure:

CountColor:= COUNT ( 'Product'[Color] )

I put it into the pivot table where 'Product'[Color] is added to the rows section, here is the result:

Row Labels	CountColor
Azure	14
Black	602
Blue	77
...	...

This shows that the filter context in each row is defined by the Color of that row.

When I am writing the following measure, I am anticipating that VALUES only return a unique value of the one color that is currently available in the row of the pivot table and it gets automatically converted to a scalar value:

CountColorValues:= CALCULATE (
                     COUNT ( 'Product'[Color] ),
                     FILTER ( 'Product',
                              'Product'[Color] = VALUES ( 'Product'[Color] )
                     )
                   )

But putting this measure to the same table, I get the error: "Calculation error in measure 'Product'[CountColorValues]: A table of multiple values was supplied where a single value was expected."

I created another measure to test if VALUES really return a single value:

CountrowsValues:= COUNTROWS ( 
                     CALCULATETABLE ( VALUES ( 'Product'[Color] )
                     )
                  )

The result:

Row Labels	CountrowsValues
Azure	1
Black	1
Blue	1
...	...

What am I missing when I am trying to make VALUES work in FILTER?

You're getting this error, because you are trying to compare a scalar value with table, as VALUES returns table, regardless of how many rows it returns. Use IN operator or CONTAINSROWS function. — W.B.
As far as I know, if a table has one column and one row, DAX automatically converts it into a scalar value if it is used in an expression. CountColorTableToScalar:=CALCULATE(COUNT('Product'[Color]), FILTER('Product','Product'[Color]=SUMMARIZE(TOPN(1,'Product'),'Product'[Color]))) In this measure the result of SUMMARIZE is passed to the FILTER expression, where the result is intentionally a one column-one row table. — SVG
Sure, but if you're always expecting one value, why use VaLUES in the first place? — W.B.
Could you also point me to a doc that talks about this implicit conversion of table to scalar? I did not find it here: docs.microsoft.com/en-us/power-bi/connect-data/… — W.B.
Here you will find under Syntax requirements the mentioning of conversion: docs.microsoft.com/hu-hu/dax/dax-syntax-reference — SVG

Jon Jon · Accepted Answer · 2021-02-15T13:20:33

In this case, VALUES gets a distinct list of items, so in the following:

CountColorValues:= CALCULATE (
                     COUNT ( 'Product'[Color] ),
                     FILTER ( 'Product',
                              'Product'[Color] = VALUES ( 'Product'[Color] )
                     )
                   )

'Product'[Color] is trying to equal a list of {'Azure', 'Black' , 'Blue'}, so in affect you are trying to make the rows of 'Azure' equal to a table of the distinct values

For this to work it needs to be changed to an IN, is the [color] value IN the list, not equal to a list.

 CountColorValues:= CALCULATE (
                         COUNT ( 'Product'[Color] ),
                         FILTER ( 'Product',
                                  'Product'[Color] IN VALUES ( 'Product'[Color] )
                         )
                       )

Using FILTER with VALUES (multiple rows error)

2 Answers