Performance Issue with EARLIER

[Mann]

Hi Guys,

In my dataset I am getting Individual IDs, Start Time, End Time and Index. 

We are recording entry and exit times of Individuals using multiple source so these Start and End Time can be overlapping.

I need to remove some records from this table which should be tagged as Flag=0, otherwise 1 based on these rules:

•  If any record has end time = max(end time) from a filtered set of records per Individual then keep the record i.e. Flag=1
•  If end time is blank keep the record therefore, Flag=1
•  If end time is < max(end time) from a filtered set of records per Individual then ignore the record i.e. Flag=0

This is the sample data:

Ind ID	Start Time	End Time	Index	Flag
A	8/1/2019 9:00		1	1
A		8/1/2019 11:00	2	1
A		8/2/2019 11:00	3	1
B	8/1/2019 9:00		1	1
B	8/1/2019 11:00		2	1
B	8/2/2019 11:00		3	1
C	8/2/2019 9:00	8/2/2019 17:00	1	1
C	8/2/2019 10:00	8/4/2019 18:00	2	1
C	8/3/2019 8:00	8/3/2019 16:00	3	0
C	8/4/2019 10:00	8/4/2019 16:00	4	0
C	8/5/2019 11:00	8/5/2019 21:00	5	1
C	8/5/2019 14:00	8/5/2019 22:00	6	1
D	8/1/2019 6:00	8/1/2019 7:00	1	1
D		8/1/2019 9:00	1	1
D	8/3/2019 8:00	8/5/2019 16:00	1	1
D	8/1/2019 7:10	8/1/2019 8:50	2	1
D		8/1/2019 11:00	2	1
D	8/3/2019 9:00	8/3/2019 16:00	2	0
D	8/1/2019 8:00	8/1/2019 9:00	3	1
D		8/2/2019 11:00	3	1
D	8/4/2019 8:00	8/4/2019 18:00	3	0
D	8/1/2019 8:55	8/1/2019 9:00	4	1
D	8/5/2019 9:00	8/5/2019 18:00	4	1
D	8/1/2019 9:15	8/1/2019 10:00	5	1
D	8/6/2019 8:00	8/6/2019 17:00	5	1
D	8/1/2019 9:15	8/1/2019 10:40	6	1
D	8/1/2019 10:05	8/1/2019 10:40	7	1
D	8/1/2019 10:45	8/1/2019 10:50	8	1
D	8/1/2019 11:00	8/1/2019 14:00	9	1
D	8/1/2019 15:00	8/1/2019 16:00	10	1
D	8/1/2019 16:00	8/1/2019 16:00	11	1
D	8/1/2019 16:30	8/1/2019 20:00	12	1
D	8/1/2019 21:30	8/1/2019 22:00	13	1
D	8/1/2019 21:30	8/1/2019 23:00	14	1
D		8/1/2019 23:00	15	1
D	8/2/2019 8:00	8/2/2019 13:00	16	1
D	8/2/2019 10:00	8/2/2019 11:00	17	0
E	8/1/2019 8:00	8/1/2019 9:00	1	1
E	8/1/2019 8:00	8/1/2019 17:00	2	1
E	8/1/2019 10:00	8/1/2019 15:00	3	0
E	8/1/2019 16:00	8/1/2019 18:00	4	1
F	8/1/2019 8:00	8/1/2019 11:00	1	1
F	8/1/2019 9:00	8/1/2019 10:00	2	0
F	8/1/2019 15:00	8/1/2019 22:00	3	1
F	8/1/2019 16:00	8/1/2019 21:00	4	0
O	8/1/2019 8:00	8/1/2019 8:35	1	1
O	8/1/2019 8:30	8/1/2019 9:00	2	1
O	8/1/2019 8:40	8/1/2019 8:45	3	0
O	8/1/2019 8:50	8/1/2019 9:30	4	1
O	8/1/2019 12:00	8/1/2019 14:00	5	1
O	8/1/2019 13:00	8/1/2019 15:00	6	1
O	8/1/2019 18:00	8/1/2019 22:00	7	1

For example: For Ind ID= C, Flag=0 for Index 3 and 4 because End time is less than End time of Index =2 for C.

I created this calculation for achieving this:

Flag = 
VAR _MaxEndTime= 
CALCULATE (
    MAX ( Table1[End Time] ),
    FILTER (
        Table1,
        AND (
            Table1[Ind ID] = EARLIER(Table1[Ind ID]),
            Table1[Index] <= EARLIER (Table1[Index] )
        )
    )
)
Return
IF(Table1[End Time]=BLANK(),1,IF(Table1[End Time]<_MaxEndTime,0,1))

This calculation works fine but I am facing huge performance issue when volume of data is big like 1Mn rows. I suspect using <=EARLIER is the cause for it. I can't find a way to write this in some other performant way.

How can we do this? Please advice.

Mann.

[v-lili6-msft]

hi @Mann 

You need to use Variables in the formula instead of EARLIER

Column 5 = 
VAR _Index = Table1[Index]
VAR _IndID = Table1[Ind ID]
VAR _MaxEndTime =
    CALCULATE (
        MAX ( Table1[End Time] ),
        FILTER ( Table1, Table1[Ind ID] = _IndID && Table1[Index] <= _Index )
    )
RETURN
    IF ( Table1[End Time] = BLANK (),1, IF ( Table1[End Time] < _MaxEndTime, 0, 1 ) )

or

Column 4 = 
var _Index=Table1[Index] return
VAR _MaxEndTime= 
CALCULATE (
    MAX ( Table1[End Time] ),
    FILTER (
        ALLEXCEPT(Table1,Table1[Ind ID]),
            Table1[Index] <= _Index
        )
    )
Return
IF(Table1[End Time]=BLANK(),1,IF(Table1[End Time]<_MaxEndTime,0,1))

https://exceleratorbi.com.au/using-variables-dax/

Regards,

Lin

[Mann]

Thanks @v-lili6-msft 

I used the code you provided.  Still it is not working as expected. It is taking forever to return results with 1Mn rows and RAM is going over 25GB.

Can we re-write this logic some other way. If we need to create more calculated column, thats fine, provided we are getting performant results.

Thanks

[Mann]

Hi

Can somebody help or advice if this can be done.

Thanks