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

[TommyNguyen]

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.

[TommyNguyen]

Thanks for you your support

[bcdobbs · Accepted solution]

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]

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