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,

 

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