Forum Discussion
Filter context not working on calculated variables
- Anonymous5 years ago
Thanks,
I was able to achieve the filter by filtering each VAR individually like so
totalexpensenorthVAR = VAR _percentagenorth = CALCULATE(SUM(expense[north]), FILTER(ALL(FYDate), FYDate[RankFinYYWW] >= MAX(FYDate[RankFinYYWW])-2 && FYDate[RankFinYYWW] <= MAX(FYDate[RankFinYYWW])-1)) VAR _goodexpense = CALCULATE(SUM(expense[allexpense]), FILTER(ALL(FYDate), FYDate[RankFinYYWW] >= MAX(FYDate[RankFinYYWW])-2 && FYDate[RankFinYYWW] <= MAX(FYDate[RankFinYYWW])-1)) VAR _fuelexpense =CALCULATE(SUM(expense[fuelexpense]), FILTER(ALL(FYDate), FYDate[RankFinYYWW] >= MAX(FYDate[RankFinYYWW])-2 && FYDate[RankFinYYWW] <= MAX(FYDate[RankFinYYWW])-1)) VAR _total = (_goodexpense+_fuelexpense)*_percentagenorth RETURN _total - 5 years ago
Hi Anonymous !
There are 2 problems with the DAX code you have written; Let's replace all VAR with explicit measures [_total];
_total = VAR _percentagenorth = SUM(expense[north]) VAR _goodexpense = SUM(expense[allexpense]) * _percentagenorth VAR _fuelexpense = SUM(expense[fuelexpense]) * _percentagenorth RETURN _goodexpense +_fuelexpense totalexpensenorth = CALCULATE([_total], FILTER(ALL(FYDate), FYDate[RankFinYYWW] >= MAX(FYDate[RankFinYYWW])-2 && FYDate[RankFinYYWW] <= MAX(FYDate[RankFinYYWW]) -1))
The other problem in your DAX was with >= & <= sign, you were using = sign for both conditions earlier.Hope this will resolve.
Regards,
Hasham
Anonymous When you create a variable like this :
VAR _Val = Calculate( ... )
It will be considered as a constant so you cannot use this variable in a future Calculate expression like this :
Calculate( _Val, Filter(...) )
You need to remove these variable and keep the original SUM expression in the calculate function
- Anonymous5 years agoNot applicable
Thanks,
I was able to achieve the filter by filtering each VAR individually like so
totalexpensenorthVAR = VAR _percentagenorth = CALCULATE(SUM(expense[north]), FILTER(ALL(FYDate), FYDate[RankFinYYWW] >= MAX(FYDate[RankFinYYWW])-2 && FYDate[RankFinYYWW] <= MAX(FYDate[RankFinYYWW])-1)) VAR _goodexpense = CALCULATE(SUM(expense[allexpense]), FILTER(ALL(FYDate), FYDate[RankFinYYWW] >= MAX(FYDate[RankFinYYWW])-2 && FYDate[RankFinYYWW] <= MAX(FYDate[RankFinYYWW])-1)) VAR _fuelexpense =CALCULATE(SUM(expense[fuelexpense]), FILTER(ALL(FYDate), FYDate[RankFinYYWW] >= MAX(FYDate[RankFinYYWW])-2 && FYDate[RankFinYYWW] <= MAX(FYDate[RankFinYYWW])-1)) VAR _total = (_goodexpense+_fuelexpense)*_percentagenorth RETURN _total