cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper V
Helper V

How does earlier work correctly when table is not ordered?

 

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])
        )
    
)

 

2 ACCEPTED SOLUTIONS
Community Champion
Community Champion

Hi @wilson_smyth

 

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

 

main-qimg-faa7fa4fc437002089f380378115e1bd.png

Regards,
Zubair


View solution in original post

@wilson_smyth

 

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

 

 

Regards,
Zubair


View solution in original post

4 REPLIES 4
Community Champion
Community Champion

Hi @wilson_smyth

 

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

 

main-qimg-faa7fa4fc437002089f380378115e1bd.png

Regards,
Zubair


View solution in original post

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?

@wilson_smyth

 

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

 

 

Regards,
Zubair


View solution in original post

@wilson_smyth

 

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
        )
    )
Regards,
Zubair


Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors