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 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!

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

     

3 Replies

  • ThxAlot's avatar
    ThxAlot
    Icon for Super User rankSuper User
    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!