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 revenue],'Product'[ProductID]>10))

 

 

This return a table that is not as expected because the revenue is the same with all productIDs:

When I modify the Dax to:

 

Revenue of productID greater than 10 = 
Var productIDContext = VALUES('Product'[ProductID])
return CALCULATE(Sales[Sum revenue],'Product'[ProductID]>10,productIDContext)

 

It returns the result as expected

In conclusion, I want to what variable productIDContext is really doing in this Dax formulae, it seems that the filter: 'Product'[ProductID]>10  has overwritten the filter context of productID outside the calculation function so we need productIDContext to regain it, but I still could not understand exactly the under the mechanism of Dax to merge the initial filter context of productID outside the Calculate function and 2 filter parameters inside the Calculate function. Can someones please explain this to me? Sorry for my bad english.

  • 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.

3 Replies

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