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
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
- Anonymous5 years agoNot applicable
Thanks for the detail explaination i assume i would have to write this as two measures?
- HashamNiaz5 years agoSolution Sage
Yes Anonymous
These are 2 measures.