Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I have a measure, which finds the most recent event a person attended. It works correctly, but im not fully sure why.
It does this via a calculate column (shown below), that uses the earlier function to count the rows.
The filter is whats confusing me. it uses
'Event Attendance'[Event Date] >= Earlier('Event Attendance'[Event Date].[Date])
As there is on order on the table, what does the engine do if the matching earlier row is not >= the earlier row?
It handles this case and returns the correct answer, but i would like to understand how exactly it works considering the table is not ordered on Event Date.
Full Code for column:
AttendanceDate Ranking = countrows( filter( 'Event Attendance', 'Event Attendance'[Event Date] >= Earlier('Event Attendance'[Event Date].[Date]) && 'Event Attendance'[Column1.ContactID] = earlier('Event Attendance'[Column1.ContactID]) ) )
Solved! Go to Solution.
EARLIER returns the CurrentRow on which the calculation is performed ......Not the PreviousOne which the name literally implies
This name is slighty confusing. Some experts suggest that the name of EARLIER should be CURRENTROW. But technically EARLIER refers to EARLIER row context which becomes hidden when a new ROW context is introduced for example by an ITERATOR
You are right
CurrentRow (retrived using EARLIER) is compared with every ROW of the Table used by the ITERATOR (FILTER is the iterator here)
All ITERATORS (like FILTER, SUMX etc) typically have two arguments...One is Table Expression, second is the Expression which is evaluated for every row of the Table passed as first argument
EARLIER returns the CurrentRow on which the calculation is performed ......Not the PreviousOne which the name literally implies
This name is slighty confusing. Some experts suggest that the name of EARLIER should be CURRENTROW. But technically EARLIER refers to EARLIER row context which becomes hidden when a new ROW context is introduced for example by an ITERATOR
Cool, that helps, but still does not make it clear how my measure can find the most recent value based on date, if the table is not sorted based on date.
Should i think of it like an itterator that just takes the first row, gets the date and then for each row, compares the dates and if the new date is newer, it now takes that one, which it returns if it finds no newer dates?
You are right
CurrentRow (retrived using EARLIER) is compared with every ROW of the Table used by the ITERATOR (FILTER is the iterator here)
All ITERATORS (like FILTER, SUMX etc) typically have two arguments...One is Table Expression, second is the Expression which is evaluated for every row of the Table passed as first argument
Eversince the Introduction of VARIABLES in DAX........In most cases you can avoid using EARLIER by using VARIABLES instead
Following DAX should give you same results as your formula
AttendanceDate Ranking = VAR CurrentRowDate = 'Event Attendance'[Event Date].[Date] VAR CurrentRowContactID = 'Event Attendance'[Column1.ContactID] RETURN COUNTROWS ( FILTER ( 'Event Attendance', 'Event Attendance'[Event Date] >= CurrentRowDate && 'Event Attendance'[Column1.ContactID] = CurrentRowContactID ) )
User | Count |
---|---|
140 | |
113 | |
104 | |
76 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |