Forum Discussion
Index column based on date, multiple entries excluded based on time
- 8 years ago
Step 1:
Index =
VAR UserFirstDate = MINX(FILTER('Events Completion';'Events Completion'[user_id] = EARLIER('Events Completion'[user_id]));'Events Completion'[DateTime])
RETURNDIVIDE (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 ))
| 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 | 1 | 12-06-18 | 20:15:18 | 2 | |
| 8ab1ed3d-523f-4926-a4f5-a4c938e693da | 1 | 13-06-18 | 16:55:40 | 3 | |
| a2119042-caf3-408d-8ed4-43f3ad197765 | 2 | 12-06-18 | 22:53:24 | 1 | |
| a8a95082-bc10-4d80-9333-41db49900df0 | 2 | 12-06-18 | 22:53:22 | 1 | |
| c3b7b029-4344-440f-a31c-8f54c84f1a73 | 3 | 11-06-18 | 15:43:11 | 1 | |
| 666a64cf-cb1d-434a-93b5-ed49fe785218 | 3 | 14-06-18 | 15:43:11 | 2 | |
| 5c3ae105-78a8-4adb-86ce-1ac670edb100 | 3 | 15-06-18 | 15:43:10 | 3 | |
| 55f913f1-c14d-4824-9d27-a27bde8b93b5 | 3 | 16-06-18 | 15:43:09 | 4 | |
| cfcde4bf-cb48-46bc-a105-4f95fba50895 | 4 | 07-06-18 | 15:44:16 | 1 | |
| f1b312ae-1f09-4870-80ba-4475f7731679 | 4 | 09-06-18 | 22:53:22 | 2 | |
| 5876a70a-eb01-4adf-9ff3-35fef3641148 | 4 | 09-06-18 | 22:53:20 | 2 | |
| The index is based on User_id per day | |||||
| If a User_id has multiple entries on the same day the index needs to be the same (see ID 2) |
Above an example of my data. I rather new to powerbi and I cannot figure out how to exclude the multiple entries (like in ID 2) in my index.
The purpose is to see how active a user is in terms of the number of days since the first day of activity.
Could anyone help me out how to resolve this?