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

[Atseaukes]

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?

[Phil_Seamark]

Hi  @Atseaukes

You could try this calculated column which takes into account date & time

Column = 
VAR UserFirstDate = MINX(FILTER('Table1','Table1'[user_id] = EARLIER('Table1'[user_id])),'Table1'[date] & " " & 'Table1'[time])
RETURN DIVIDE(DATEDIFF(UserFirstDate ,'Table1'[date] & " " & 'Table1'[time],HOUR),24)+1

Can adjusted as needed

[Atseaukes]

Hi @Phil_Seamark,

I used:

Index =
VAR UserFirstDate = MINX(FILTER('Events Completion';'Events Completion'[user_id] = EARLIER('Events Completion'[user_id]));'Events Completion'[date] & " " & 'Events Completion'[time])
RETURN DIVIDE(DATEDIFF(UserFirstDate ;'Events Completion'[date] & " " & 'Events Completion'[time];HOUR);24)+1

but somehow that didn't work. See the example hereunder.

 

It gave back negative results and decimals which, in addition, are not the same for the same day... 

Any clue?

[Atseaukes]

I made some changes:

DayOfUse = 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

But this results in the day number after first use. So startdate gives value 1. Second date is af day 10 gives value 10.

How to change the second (thirth, forth, etc) day into value 2?

[Phil_Seamark]

Hi @Atseaukes

Is the time of day important?

Eg, if the first item starts at 2pm (in the afternoon) on a Monday.

In that case, what value would you give to an event that took place at 10am on the following Wednesday, and also to one that took place at 8pm on the same Wednesday?  

Do you need to track that one of those events was less than 48 hours while the other is more than 48 hours to give a different result?  Or should both have the same value because the time component is not important?

[Atseaukes]

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]

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]

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]

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 · Accepted solution]

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]

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?