cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Mona01 Frequent Visitor
Frequent Visitor

Nested Calculate to compute Simple moving Average

Hi, I am new in Dax. I am just going through an article which compute simple moving average.
As per my understanding, it is a calculated column So there is a row context. So the VALUES(SMoving[DayNumber]) have a value of current row.


That query will give me a filter rows on which sum have been calculated.

Savrage= CALCULATE (
sum ( SMoving[Close]),
SMoving[DayNumber] >= VALUES ( SMoving[DayNumber] ) - 3,
SMoving[DayNumber] <= VALUES ( SMoving[Date] )
)

But it is not working. the correct query is given below. Can anyone tell what is happning here.

Savrage= CALCULATE

(    

CALCULATE (        AVERAGE ( Prices[Close] ),        Prices[DayNumber] >= VALUES ( Prices[DayNumber] ) - 3,        Prices[DayNumber] <= VALUES ( Prices[DayNumber] )   ),   ALLEXCEPT ( Prices, Prices[Stock], Prices[DayNumber] ))

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User
Super User

Re: Nested Calculate to compute Simple moving Average

Hi @Mona01

 

VALUES( ) is not be affected by row context, only by filter context. If you use VALUES( Table1[Column1] ) directly in a calculated column, you will get a one-column table with the unique values in Table1[Column1], since there is no filter context. 

 

In the second example that you show, the outermost CALCULATE however converts the row context into a filter context through context transition. In this way, when VALUES( ) is evaluated, there is a filter context that actually filters the current row (and its duplicates, if there are any) and therefore VALUES ( Prices[DayNumber] ) will return the value of Prices[DayNumber] in the current row.      

 

You might want to take a look at this and this articles by the Italian gurus. 

View solution in original post

2 REPLIES 2
Highlighted
Super User
Super User

Re: Nested Calculate to compute Simple moving Average

Hi @Mona01

 

VALUES( ) is not be affected by row context, only by filter context. If you use VALUES( Table1[Column1] ) directly in a calculated column, you will get a one-column table with the unique values in Table1[Column1], since there is no filter context. 

 

In the second example that you show, the outermost CALCULATE however converts the row context into a filter context through context transition. In this way, when VALUES( ) is evaluated, there is a filter context that actually filters the current row (and its duplicates, if there are any) and therefore VALUES ( Prices[DayNumber] ) will return the value of Prices[DayNumber] in the current row.      

 

You might want to take a look at this and this articles by the Italian gurus. 

View solution in original post

Mona01 Frequent Visitor
Frequent Visitor

Re: Nested Calculate to compute Simple moving Average

I would like to know regarding the above query .the below dax is also used calculate and data is also filtring why there is no filter context.

 

Savrage= CALCULATE (
sum ( SMoving[Close]),
SMoving[DayNumber] >= VALUES ( SMoving[DayNumber] ) - 3,
SMoving[DayNumber] <= VALUES ( SMoving[Date] )
).

 

for 2nd query

 

 CALCULATE

(    

CALCULATE (        AVERAGE ( Prices[Close] ),        Prices[DayNumber] >= VALUES ( Prices[DayNumber] ) - 3,        Prices[DayNumber] <= VALUES ( Prices[DayNumber] )   ),   ALLEXCEPT ( Prices, Prices[Stock], Prices[DayNumber] ))

 

If we remove ALLEXCEPT ( Prices, Prices[Stock], Prices[DayNumber] ) function . what happen

 

what is the differnce when calculate use with no filter parameter Vs ALLEXCEPT

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 206 members 2,155 guests
Please welcome our newest community members: