Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

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])* _...
  • Anonymous's avatar
    Anonymous
    5 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
  • HashamNiaz's avatar
    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