cancel
Showing results for
Did you mean:
Mona01 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

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.

2 REPLIES 2
Highlighted 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.

Mona01 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

Announcements Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge. 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. Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag. Top Kudoed Authors
Users Online
Currently online: 206 members 2,155 guests
Recent signins:
• rgstevens • VIZYUL • elainechen888 • Kirthi12 • mauriciosotero • sonam7 • rajendran • pyhchan • peterkim • poconnor • branden_dahlem • nspolmann • LBremmer • njaved 