Forum Discussion
Associate two different lines with each other
Hey,
I have a database that keeps track of chat sessions that looks something like this:
| id | session_id | operation_id | result_category | result_value | timestamp |
| 1 | b3612c2f-5a3d-4c81-a7ae-fafef5b27fb1 | BOT_START | null | null | 2019-12-01 00:17:40 |
| 2 | b3612c2f-5a3d-4c81-a7ae-fafef5b27fb1 | READING_REGISTER | 2 | 6 | 2019-12-01 00:17:53 |
| 3 | b3612c2f-5a3d-4c81-a7ae-fafef5b27fb1 | GOODBYE | null | null | 2019-12-01 00:18:02 |
| 4 | b3612c2f-5a3d-4c81-a7ae-fafef5b27fb1 | RATING | 1 | 4 | 2019-12-01 00:18:05 |
The "result_category" tells me what kind of result to expect (success/insuccess, integers, accept/don't accept, etc.) and "result_value" tells me what the actual result is (so category 2, result 6 means it's a success; category 1, result 4 means that the rating was 4 stars out of 5; etc.).
The "session_id" is a random value assigned to each session (the same user might have different sessions with different ids).
The problem is, I want to associate the "rating" operation to other operations such as "reading_register" so that I know that users, on average, rate operation X with Y stars. There are other operations such as "bot_start" or "goodbye" that I want to ignore because the user's rating isn't about those. How do I do this?
3 Replies
Can you explain output with example
Hi Anonymous,
You can try to convert your table fields with 'group' and 'transpose' functions(power query) to convert specific rows to table columns. Then you can simply summarize rating column values with specific conditions.
If you can't convert your table structure, you can write a measure formula to calculate with variables steps.
Steps:
1. Filter on the table to check 'operation_id' equal to 'READING_REGISTER' and result category and value suitable for your requirement and extract session_id list.
2. Use extracted session_id list to filter table records and choose 'operation_id' equal to 'RATING', then do aggregate on result value of filtered records.Regards,
Xiaoxin Sheng
