Difference in hours between duplicates

[Anonymous]

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

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

[Anonymous]

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

[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.  

[Anonymous]

@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

[amitchandak · Accepted solution]

Please find the attached solution after signature

[Anonymous]

@amitchandak thank you so much for your solution. Looks like exacly what I need.

For some reason, it is constantly giving me a memory error. Even with small amounts of data. 

[amitchandak]

@Anonymous , check the new two columns were I split the calculations

[Anonymous]

@amitchandak thank you very much for your help. 

I think this wont work in Import Mode since it is always giving me the memory error.

And it doesnt work in direct query, right?

[amitchandak]

@Anonymous , I created it as a measure. Check Measure and Measure 2. No need to create columns

[parry2k]

@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.

[Anonymous]

Hey @parry2k 

Thank you for the reply.

There can be several id's. 

Up to 30 duplicates.