Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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] ))
Solved! Go to Solution.
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.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
93 | |
77 | |
65 | |
50 |
User | Count |
---|---|
147 | |
106 | |
104 | |
87 | |
61 |