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

Highlighted
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

#### 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.

#### Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

#### Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 42 members 1,190 guests
Recent signins: