Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
Anonymous
Not applicable

Aggregate, Sort the data -> Calculate score

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.

 

shanekim_0-1611733672306.png

 

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

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

 

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

View solution in original post

9 REPLIES 9
amitchandak
Super User
Super User

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

 

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube
Anonymous
Not applicable

YOU ARE THE HERO THANK YOU SO MUCH

Anonymous
Not applicable

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

 

shanekim_0-1611796372666.png

 

 

amitchandak
Super User
Super User

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

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube
Anonymous
Not applicable

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

@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

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube
Anonymous
Not applicable

@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
Super User
Super User

can you provide the data in text format?





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




Anonymous
Not applicable

Here is a link to the pbix file. @vanessafvg 

https://we.tl/t-TM2i36iWpY 

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.