Forum Discussion
Anonymous
5 years agoNot applicable
Filter context not working on calculated variables
Hi I have code as such - this is suppose to break down the expenses for north region: TotalExpense =
VAR _percentage = SUM(north%)
VAR _goodexpense = SUM(expense[allgoodexpense])* _...
- 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
5 years agoNot applicable
Thanks for the detail explaination i assume i would have to write this as two measures?
HashamNiaz
Solution Sage
5 years agoYes Anonymous
These are 2 measures.