Understanding interaction of Iterators and VALUES()

[gazpage]

Hi,

I wanted to work out the change in a value for the mileage of each vehicle over the month and the average that.

I eventually came up with a measure like this.

[Average Mileage] :=
AVERAGEX (
VALUES ( Date[VehicleID ] ),
CALCULATE ( MAX ( Data[Mileage] ) - MIN ( Data[Mileage] ) )
)

I can understand at a high level what is going on? But I am keen to understand more specifically, in particular why the CALCULATE is needed. I guess I don't understand why I am in a row context that needs to be converted to a filter context.

Thanks
Gary

 

[akice]

AVERAGEX is an iterator (and iterators create a row context). In your case, your call to VALUES returns all the unique values in the Date[Vehicle] column visible in the current filter context. So for each value of VehicleID you want to compute the value of MAX ( Data[Mileage] ) - MIN ( Data[Mileage] ). You need the call to CALCULATE because one of its primary uses is to convert the row context created by AVERAGEX into a filter context so you only get the MIN and MAX values for the currently iterated VehicleID.

If you did not include a call to CALCULATE, AVERAGEX would still iterate over every value of VehicleID; however the values returned from MAX and MIN calls would be relative to all the VehicleID's and not just the currently iterated row. MAX and MIN would return the same value for every iteration. And then the AVERAGE of MAX - MIN calculation will be the same number as generated for each row.

Remember there are 2 contexts in Dax : row context and filter contexts. But only filter contexts alter the foundset of data against which aggregations are computed. If we want a row context to change the foundset, we need a trigger to remove it from row context and put it into the filter context. CALCULATE is one of the triggers. But there are many other table functions that internally use CALCULATE and trigger context transition: CALCULATETABLE, FIRSTDATE, LASTDATE, FIRSTNONBLANK, LASTNONBLANK and more. Context transition is a hugely important concept to understand.

Hope this helps...

 

[akice]

Edit

 

[akice]

But after i posted this i got to thinking and in the particular case you listed, AVERAGEX should perform the transition for you and the CALCULATE should not be necessary. You should get the same results either way.

Context transition and following the flow of filter contexts is tricky and easy to get messed up on. I certainly have a ways to go to fully understand.

 

[gazpage]

Without the CALCULATE it definitely calculated Max of all vehicles less Min of all vehicles.