topic Re: Regarding using VALUES as a filter argument in CALCULATE in DAX Commands and Tips

Regarding using VALUES as a filter argument in CALCULATE

HungFan — Wed, 28 Jun 2023 02:54:13 GMT

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!

Re: Regarding using VALUES as a filter argument in CALCULATE

Jihwan_Kim — Wed, 28 Jun 2023 03:08:45 GMT

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.

Re: Regarding using VALUES as a filter argument in CALCULATE

HungFan — Wed, 28 Jun 2023 04:19:22 GMT

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!

Re: Regarding using VALUES as a filter argument in CALCULATE

ThxAlot — Wed, 28 Jun 2023 04:52:25 GMT

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] ) )