Forum Discussion
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] ))
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
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.
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
