Forum Discussion
Difference in hours between duplicates
Hi everyone 🙂
I am having a problem, hope you can help.
I need to have the differente in hours between duplicates. Example:
Date Time SESSION_ID
24/01/2020 10:00 100
24/01/2020 11:00 100
14/03/2020 12:00 290
16/03/2020 13:00 254
In session_ID column, there are two duplicates, the sessio_id=100.
I need to know the difference in hours between those sessions, which would be 1 hour.
I already have a measure that count how many times the same session_id appears. But am strugling with the rest.
Does anyone has any type of clue on how this could be done in power bi?
Thank you so much for your help 🙂
Stay safe,
Alena
Please find the attached solution after signature
11 Replies
- parry2k
Super User
Anonymous do you expect one duplicate record for each session_id or it can be more than one? The solution will depends on the answer.
- amitchandak
Anonymous ,
Not sure I got it completely. But a new column will give you count in each row
countx(filter(Table,Table[SESSION_ID] = earlier(Table[SESSION_ID])),Table[Date Time ])
- parry2k
Anonymous let me rephrase my question, same session id can have more than one duplicate, if yes, let's say session id 111 has 10 rows then how your calculation would look like? I'm not going to blindly provide a solution when I didn't know the problem in-depth.
parry2k sorry for not being explicit.
If an id has 10 duplicates, I need to have the time difference in hours between consecutive id's. An example:
Session_ID Date Time The column I need
111 10/04/2020 10:00 NaN
111 10/04/2020 11:00 1
111 10/04/2020 12:00 1
111 10/04/2020 15:00 3
111 11/04/2020 01:00 10
112 11/04/2020 10:00 NaN
111 11/04/2020 11:00 10
Hi amitchandak
Thank you for your reply.
What would be the output of that formula?
I tried using it, and somehow you wrote the "earlier" formula incorreclty. Also, this earlier formula won't let me input a date time.
Thank you,
Alena
