Forum Discussion
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])* _percentage
VAR _fuelexpense = SUM(expense[allfuel])* _percentage
VAR _total = _goodexpense + _fuelexpense
RETURN CALCULATE(_total,
FILTER(ALL(FYDate), FYDate[RankFinYYWW] = MAX(FYDate[RankFinYYWW])-6
&& FYDate[RankFinYYWW] = MAX(FYDate[RankFinYYWW]) -1))Which should filter my visual between last week and 6 weeks previous.
But at the moment its returning all weeks I have in my Calender, I only want the hightlighted yellow
Not sure where I went wrong here as the filter works fine for other measures example:
AllTotalExpense =
CALCULATE(SUM(expense[allgoodexpense]),
FILTER(ALL(FYDate),
FYDate[RankFinYYWW] >= MAX(FYDate[RankFinYYWW])-6 &&
FYDate[RankFinYYWW] <= MAX(FYDate[RankFinYYWW])-1)))
Please help - let me know if you need sample data as I can create some mockup data as well.
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 _totalHi 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
7 Replies
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
Yes Anonymous
These are 2 measures.
Hi,
Unable to understand your question. Share the link from where i can download your PBI file and clearly show the expected resut there.
Hi I have made a sample PBIX file.
First time trying to share it so let me know if it doesnt work.
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 functionThanks,
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
