Forum Discussion

HungFan's avatar
HungFan
Regular Visitor
3 years ago
Solved

Regarding using VALUES as a filter argument in CALCULATE

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 ...
values
  • ThxAlot's avatar
    ThxAlot
    3 years ago

    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's avatar
Jihwan_Kim
Icon for Super User rankSuper User
3 years ago

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's avatar
HungFan
Regular Visitor
3 years ago

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!