Forum Discussion

Atseaukes's avatar
Atseaukes
Frequent Visitor
8 years ago
Solved

Index column based on date, multiple entries excluded based on time

Current colomns        Desired column action id user_id date time   Index 2ac9756d-d46d-42ff-b759-6fb88beaf8f7 1 11-06-18 11:35:40   1 30ac92ed-8e81-4443-aed7-8261eb0a597c ...
  • Atseaukes's avatar
    Atseaukes
    8 years ago

    Step 1:

    Index =

    VAR UserFirstDate = MINX(FILTER('Events Completion';'Events Completion'[user_id] = EARLIER('Events Completion'[user_id]));'Events Completion'[DateTime])
    RETURN

    DIVIDE (DATEDIFF(UserFirstDate ;'Events Completion'[DateTime];DAY);1)+1

     

    Step 2:

    Rank =
    VAR d = 'Events Completion'[Index]
    VAR c = 'Events Completion'[user_id]
    RETURN
    CALCULATE (
    RANK.EQ ( d; 'Events Completion'[Index]; ASC );
    FILTER ( ALL ( 'Events Completion'); 'Events Completion'[user_id] = c ))

Atseaukes's avatar
Atseaukes
Frequent Visitor
8 years ago

Hi Phil_Seamark,

 

The time of day is not important.

 

Monday would have value 1. The following Wednesday at 10am would have value 2, just like the event at 8pm (value = 2).

Phil_Seamark's avatar
Phil_Seamark
Icon for Microsoft Employee rankMicrosoft Employee
8 years ago

ok, then does this get closer?

 

Column = 
VAR UserFirstDate = MINX(FILTER('Table1','Table1'[user_id] = EARLIER('Table1'[user_id])),'Table1'[date] )
RETURN DATEDIFF(UserFirstDate ,'Table1'[date] ,DAY)+1
  • Atseaukes's avatar
    Atseaukes
    Frequent Visitor
    8 years ago

    Hi Phil_Seamark,

     

    On the subsequent days the value is correct. However, if there are more days in between, those days are added to the value. The values must be an orderly one. 

     

    Like in this example (all have the same user_id):

    1-06-18, 12:00 ; value 1

    1-06-18, 13:00 ; value 1

    2-06-18, 12:00 ; value 2

    4-06-18, 12:00 ; value 3 (instead of value 4, which will be the result of the formula I posted)

    • Atseaukes's avatar
      Atseaukes
      Frequent Visitor
      8 years ago

      Hi Phil_Seamark,

       

      Do you have any suggestions what to do else?

      Maybe doable with a RANKX? 

      Rank = RANKX('Events Completion';'Events Completion'[Index];;ASC;Dense)

      But first it needs to be filtered by 'Events Completion'[User_ID] I guess, right?

      • Atseaukes's avatar
        Atseaukes
        Frequent Visitor
        8 years ago

        Step 1:

        Index =

        VAR UserFirstDate = MINX(FILTER('Events Completion';'Events Completion'[user_id] = EARLIER('Events Completion'[user_id]));'Events Completion'[DateTime])
        RETURN

        DIVIDE (DATEDIFF(UserFirstDate ;'Events Completion'[DateTime];DAY);1)+1

         

        Step 2:

        Rank =
        VAR d = 'Events Completion'[Index]
        VAR c = 'Events Completion'[user_id]
        RETURN
        CALCULATE (
        RANK.EQ ( d; 'Events Completion'[Index]; ASC );
        FILTER ( ALL ( 'Events Completion'); 'Events Completion'[user_id] = c ))