topic Re: FILTER() in CALCULATE () with VALUES() breaks aggreagtion "scope" in DAX Commands and Tips

FILTER() in CALCULATE () with VALUES() breaks aggreagtion "scope"

Anonymous — Thu, 31 Aug 2023 08:32:52 GMT

Hello,

I have the following Measure:

SUMX ( 'DC Volume', DIVIDE ( CALCULATE ([Delivery Line Count OSR], VALUES ('DC Volume'[FCRateKey])), CALCULATE ( SUM ( Forecast_ProductivityRate[Productivity Rate] ), VALUES ( Forecast_ProductivityRate[FCRateKey] ) ) ) )

This produces the output of 1646.829 (which is correct) when put in a card visual. To visualize the result:

If I add a FILTER() expression like this:

OSR_FC_Rate_Worked_Hours = SUMX ( 'DC Volume', DIVIDE ( CALCULATE ([Delivery Line Count OSR], VALUES ('DC Volume'[FCRateKey])), CALCULATE ( SUM ( Forecast_ProductivityRate[Productivity Rate] ), VALUES ( Forecast_ProductivityRate[FCRateKey] ), FILTER ( Forecast_ProductivityRate, Forecast_ProductivityRate[From Date] >= [Selected Date SOMONTH] && Forecast_ProductivityRate[To Date] <= [Selected Date EOMONTH] && Forecast_ProductivityRate[ActivityNM / LABROLEV4ID] = "802-91" ) ) ) )

Then the aggregation specififed with VALUES() breaks. Instead of summing up all the results of each division, it uses the total:

How can I avoid this behaviour?

The two tables DC Volume and Forecast_ProductivityRate have a many to one relationship (multiple lines in DC Volume relate to one in Forecast) based on FCRateKey (this is also a unique key in Forecast_ProductivityRate).

Thanks & br

Re: FILTER() in CALCULATE () with VALUES() breaks aggreagtion "scope"

lbendlin — Sat, 02 Sep 2023 22:32:08 GMT

What's the point of using VALUES in the CALCULATE? Should not make any difference?

Re: FILTER() in CALCULATE () with VALUES() breaks aggreagtion "scope"

technolog — Sun, 24 Sep 2023 07:57:53 GMT

I hope this message finds you well. I've noticed that this solution remain unresolved. If any of you have managed to find a resolution to the issue, I kindly request that you share your solution for the benefit of the entire community. Alternatively, if you're still facing challenges, please do let us know as well.

Your insights and updates will greatly assist others who might be encountering the same challenge.