Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
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
AlB
Community Champion
Community Champion

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
AlB
Community Champion
Community Champion

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

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
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.