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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
wilson_smyth
Post Patron
Post Patron

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
Zubair_Muhammad
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

Please try my custom visuals

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

Please try my custom visuals

View solution in original post

4 REPLIES 4
Zubair_Muhammad
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

Please try my custom visuals

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

Please try my custom visuals

@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

Please try my custom visuals

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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