topic Re: Help understanding the function VALUES when passed as a filter parameter in Calculation in Dax in DAX Commands and Tips

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

TommyNguyen — Fri, 10 Dec 2021 15:05:40 GMT

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.

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

bcdobbs — Fri, 10 Dec 2021 15:27:33 GMT

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.

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

AlexisOlson — Fri, 10 Dec 2021 18:12:54 GMT

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

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

TommyNguyen — Sat, 11 Dec 2021 16:43:04 GMT

Thanks for you your support