Regarding using VALUES as a filter argument in CALCULATE

[HungFan]

Hi all,
I had learned that writing a measure of AllSalesCurrentYear, especially when computing a percentage over the grand total of only the current year, shall use VALUES('Date'[Year]) as a filter argument in CALCULATE. The V1 code as follows,

AllSalesCurrentYear V1 = 
CALCULATE(
        [Sales Amount],
        ALL('Sales'),
        VALUES('Date'[Year])
)

I know that VALUES only return the list of values of a column in the current filter context. But for me, it seems a bit weird to only use VALUES('Date'[Year]) as a filter argument. The reason is that the output of VALUES('Date'[Year]) is a unique list like the photo below,

I thought the code shall change to 'Date'[Year] IN VALUES('Date'[Year]) that makes more sense for me. And then after trying the code, I found those result are the same. The V2 code is as follows,

AllSalesCurrentYear V2 =
CALCULATE(
        [Sales Amount],
        ALL('Sales'),
        'Date'[Year] IN VALUES('Date'[Year])
)

The matrix is as follows,

Does anyone has a better understanding of the V1 code? How to explain for people that can only use VALUES('Date'[Year]) as a filter argument for this case.

Thanks!

[ThxAlot · Accepted solution]

VALUES - DAX Guide

VALUES() returns a table, which functions as filter in the calculation.

IN - DAX Guide

'Date'[Year] IN VALUES('Date'[Year]) is syntactic sugar for the CONTAINSROW(), it's interchangeable with

CONTAINSROW (
    VALUES('Date'[Year]),
    'Date'[Year]
)

in your case, an alternative to V1 is

= CALCULATE( [Sales Amount], ALLEXCEPT( 'Sales', 'Date'[Year] ) )

[Jihwan_Kim]

Hi,

In my opinion, in DAX Studio, it shows all years because there is no filter context.

In the table visualization, there is a filter context in each column, and in the 2007 column, for instance, VALUES ('Date[Year] ) = 2007.

I hope this explains.

Thank you.

[HungFan]

Hi @Jihwan_Kim ,

Thanks for the explaination. However, my question is more like how to explain why can only use VALUES('Date'[Year]) as a filter argument unless it's a modifier like ALL. Normal predicate(filter argument) are like,

'Product'[Color] = "Red" or
'Date'[Year] = 2008

That's why I said using the code of 'Date'[Year] IN VALUES('Date'[Year]) makes more sense for me. Can you get my point of view? Thanks!