Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
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
Can you explain output with example
Hi! Ideally, I'd like to have a table in Power BI something like this:
operation_id | average_rating |
READING_REGISTER | 4.2 |
DOWNLOAD_PDF | 3.5 |
PAYMENT_DATA | 3.3 |
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
97 | |
86 | |
70 | |
62 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |