Forum Discussion

Mann's avatar
Mann
Resolver III
6 years ago

Performance Issue with EARLIER

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 IDStart TimeEnd TimeIndexFlag
A8/1/2019 9:00 11
A 8/1/2019 11:0021
A 8/2/2019 11:0031
B8/1/2019 9:00 11
B8/1/2019 11:00 21
B8/2/2019 11:00 31
C8/2/2019 9:008/2/2019 17:0011
C8/2/2019 10:008/4/2019 18:0021
C8/3/2019 8:008/3/2019 16:0030
C8/4/2019 10:008/4/2019 16:0040
C8/5/2019 11:008/5/2019 21:0051
C8/5/2019 14:008/5/2019 22:0061
D8/1/2019 6:008/1/2019 7:0011
D 8/1/2019 9:0011
D8/3/2019 8:008/5/2019 16:0011
D8/1/2019 7:108/1/2019 8:5021
D 8/1/2019 11:0021
D8/3/2019 9:008/3/2019 16:0020
D8/1/2019 8:008/1/2019 9:0031
D 8/2/2019 11:0031
D8/4/2019 8:008/4/2019 18:0030
D8/1/2019 8:558/1/2019 9:0041
D8/5/2019 9:008/5/2019 18:0041
D8/1/2019 9:158/1/2019 10:0051
D8/6/2019 8:008/6/2019 17:0051
D8/1/2019 9:158/1/2019 10:4061
D8/1/2019 10:058/1/2019 10:4071
D8/1/2019 10:458/1/2019 10:5081
D8/1/2019 11:008/1/2019 14:0091
D8/1/2019 15:008/1/2019 16:00101
D8/1/2019 16:008/1/2019 16:00111
D8/1/2019 16:308/1/2019 20:00121
D8/1/2019 21:308/1/2019 22:00131
D8/1/2019 21:308/1/2019 23:00141
D 8/1/2019 23:00151
D8/2/2019 8:008/2/2019 13:00161
D8/2/2019 10:008/2/2019 11:00170
E8/1/2019 8:008/1/2019 9:0011
E8/1/2019 8:008/1/2019 17:0021
E8/1/2019 10:008/1/2019 15:0030
E8/1/2019 16:008/1/2019 18:0041
F8/1/2019 8:008/1/2019 11:0011
F8/1/2019 9:008/1/2019 10:0020
F8/1/2019 15:008/1/2019 22:0031
F8/1/2019 16:008/1/2019 21:0040
O8/1/2019 8:008/1/2019 8:3511
O8/1/2019 8:308/1/2019 9:0021
O8/1/2019 8:408/1/2019 8:4530
O8/1/2019 8:508/1/2019 9:3041
O8/1/2019 12:008/1/2019 14:0051
O8/1/2019 13:008/1/2019 15:0061
O8/1/2019 18:008/1/2019 22:0071

 

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.

Need Help

3 Replies

  • v-lili6-msft's avatar
    v-lili6-msft
    Community Support
    6 years ago

    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's avatar
      Mann
      Resolver III
      6 years ago

      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's avatar
        Mann
        Resolver III
        6 years ago
        Hi

        Can somebody help or advice if this can be done.

        Thanks