Forum Discussion

TommyNguyen's avatar
TommyNguyen
New Member
4 years ago
Solved

Help understanding the function VALUES when passed as a filter parameter in Calculation in Dax

Hi everyone, I want to display the revenue of each product where its productID is greater than 10. I have a Dax formula as bellow:   Revenue of productID greater than 10 := CALCULATE(Sales[Sum re...
  • bcdobbs's avatar
    bcdobbs
    4 years ago

    Hi,

    When you write 

    CALCULATE(
  [Sum revenue],
  'Product'[ProductID]>10
)

     

    DAX actually goes away and does this:

    CALCULATE(
  [Sum revenue],
  FILTER(
    ALL('Product'[ProductID]),
    'Product'[ProductId] > 10
  )
)

     eg it removes any existing filters on product id (eg the ones coming from the matrix rows and then it adds a new filter to just get ids greater than 10.

     

    In the measure that is working it does the same but VALUES('Product'[ProductId]) add back the existing Product Ids that were in the filter context to start with.

bcdobbs's avatar
bcdobbs
Community Champion
4 years ago

Hi,

When you write 

CALCULATE(
  [Sum revenue],
  'Product'[ProductID]>10
)

 

DAX actually goes away and does this:

CALCULATE(
  [Sum revenue],
  FILTER(
    ALL('Product'[ProductID]),
    'Product'[ProductId] > 10
  )
)

 eg it removes any existing filters on product id (eg the ones coming from the matrix rows and then it adds a new filter to just get ids greater than 10.

 

In the measure that is working it does the same but VALUES('Product'[ProductId]) add back the existing Product Ids that were in the filter context to start with.

AlexisOlson's avatar
AlexisOlson
Super User
4 years ago

bcdobbs is correct. VALUES is restoring the local ProductId filter context that CALCULATE replaces.

 

I just wanted to suggest using KEEPFILTERS in your measure like this rather than VALUES:

CALCULATE (
    Sales[Sum revenue],
    KEEPFILTERS ( 'Product'[ProductID] > 10 )
)