Associate two different lines with each other

[Anonymous]

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? 

[v-shex-msft]

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

[amitchandak]

Can you explain output with example

[Anonymous]

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