Filter context, context transition while adding a column using dax

[Anonymous]

Hey Guys, i am new to power bi and need help in understanding a few basic concepts regarding filter context and context transition and the application orders in adding a new columns via DAX. Below are 4 dax formulas and i want to understand how they work, as i am not able to decode the output perfectly:

1) a1 = CALCULATE(

AVERAGE(Prices[close]),FILTER(ALL(Prices[Date]),AND(Prices[Date]>LOOKUPVALUE(Prices[Date],Prices[Stock],EARLIER(Prices[Stock]),Prices[Day Number],EARLIER(Prices[Day Number]) - 5 ),Prices[Date]<=EARLIER(Prices[Date]))))

2) a2 = CALCULATE(

AVERAGE(Prices[close]),FILTER(ALL(Prices[close]),AND(Prices[Date]>LOOKUPVALUE(Prices[Date],Prices[Stock],EARLIER(Prices[Stock]),Prices[Day Number],EARLIER(Prices[Day Number]) - 5 ),Prices[Date]<=EARLIER(Prices[Date]))))

3) a3 = CALCULATE(

AVERAGE(Prices[close]),FILTER(ALL(Prices[Date]),AND(Prices[Date]>=LOOKUPVALUE(Prices[Date],Prices[Stock],EARLIER(Prices[Stock]),Prices[Day Number],EARLIER(Prices[Day Number]) - 5 ),Prices[Date]<=EARLIER(Prices[Date]))),ALLEXCEPT(Prices,Prices[Stock]))

4) a4 = CALCULATE(

AVERAGE(Prices[close]),FILTER(ALL(Prices[close]),AND(Prices[Date]>=LOOKUPVALUE(Prices[Date],Prices[Stock],EARLIER(Prices[Stock]),Prices[Day Number],EARLIER(Prices[Day Number]) - 5 ),Prices[Date]<=EARLIER(Prices[Date]))),ALLEXCEPT(Prices,Prices[Stock]))

I basically just want to understand the theory behind all of these.

Thanks in advance

[v-kelly-msft · Accepted solution]

Hi  @Anonymous ,

For a1,it means that you are getting an average value of "Prices[close]",which have filters of "FILTER(ALL(Prices[Date]),AND(Prices[Date]>LOOKUPVALUE(Prices[Date],Prices[Stock],EARLIER(Prices[Stock]),Prices[Day Number],EARLIER(Prices[Day Number]) - 5 ),Prices[Date]<=EARLIER(Prices[Date]))))"

For function earlier you can understand as the current row.

For "lookupvalue"is to get a related value from another column.

For references,pls see below:

Calculate: https://docs.microsoft.com/en-us/dax/calculate-function-dax

Filter: https://docs.microsoft.com/en-us/dax/filter-function-dax

Average: https://docs.microsoft.com/en-us/dax/average-function-dax

Earlier:  https://docs.microsoft.com/en-us/dax/earlier-function-dax

Lookupvalue:  https://docs.microsoft.com/en-us/dax/lookupvalue-function-dax

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!