cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ladyhaley
Resolver I
Resolver I

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

ladyhaley_1-1624573780252.png

 

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)))

 

ladyhaley_2-1624574409916.png


Please help - let me know if you need sample data as I can create some mockup data as well. 

2 ACCEPTED SOLUTIONS

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

View solution in original post

HashamNiaz
Solution Sage
Solution Sage

Hi @ladyhaley !

 

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

 

 

View solution in original post

7 REPLIES 7
HashamNiaz
Solution Sage
Solution Sage

Hi @ladyhaley !

 

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

 

 

Thanks for the detail explaination i assume i would have to write this as two measures?

Yes @ladyhaley  

 

These are 2 measures.

Kucrapok
Helper I
Helper I

@ladyhaley 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

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
Ashish_Mathur
Super User
Super User

Hi,

Unable to understand your question.  Share the link from where i can download your PBI file and clearly show the expected resut there.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hi I have made a sample PBIX file.

First time trying to share it so let me know if it doesnt work. 

sample file.pbx 

Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Show Episode 10 Recap

The Power BI Community Show

Watch the playback when Amit Chandak, a Power BI Super User, demos how to use Field Parameters to make reports more dynamic.

Power BI Dev Camp Session 26

New Date - Check it Out!

Mark your calendars and join us on Thursday, October 6 at 11a PDT for a great session with Ted Pattison!

Health and Life Sciences Power BI User Group

Health and Life Sciences Power BI User Group

Power BI specialists at Microsoft have created a community user group where customers in the provider, payor, pharma, health solutions, and life science industries can collaborate.

Top Solution Authors