Aggregate, Sort the data -> Calculate score

[Anonymous]

Sample pbix file link:

https://we.tl/t-TM2i36iWpY

I'm struggling with creating a PairScore column as shown in the screenshot below. Consider this table as a chat room log data where user A and B are sending message to each other.

Here is a logic:
(a) Aggregate the data by RoomID (Chat room) and Sort it by Date (with Ascending order).

(b) PairScore starts with 0.5. And it stays as 0.5 until the opposite user replies, which is then added 0.5 => Thus, it becomes 1. And it repeats...

I've struggled with this calculated column for days. Can someone please help? Thank you so much.

[amitchandak · Accepted solution]

@Anonymous , check if the attached file after signature can help

[Anonymous]

YOU ARE THE HERO THANK YOU SO MUCH

[Anonymous]

@amitchandak  Unfortunately I found an error on this method. As shown in the screenshot below, it needs to become 1 when client replies to partner's first message, and then becomes 1.5 when partner replies. Therefore, Final measure needs to be 1.5 

I really appreciate your time but would you please help? THanks!

[amitchandak]

@Anonymous , In case you need pairscore

You can try measure like

countx(allselected(Table), Table[roomID] = max(Table[RoomID]) && Table[Date] <= max(Table[Date])) *.5

otherwise

Can you please share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[Anonymous]

Here is a sample data! https://we.tl/t-920BuxuX7U 

I tried your method but it looks like the measure doesn't take the date column... 

[amitchandak]

@Anonymous , Create a date column from created_at, is has a time stamp

Date = [created_at].date

or

Date = date(year([created_at]),month([created_at]),day([created_at]))

You can try the same formula with correct column names

[Anonymous]

@amitchandak  Thanks but I found another issue with that method. It needs to have both date and time, but ".date" makes it all the same time.. 

Would you please help? Here is a pbix file link! https://we.tl/t-TM2i36iWpY 

Thank you so much.

[vanessafvg]

can you provide the data in text format?

[Anonymous]

Here is a link to the pbix file. @vanessafvg 

https://we.tl/t-TM2i36iWpY