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.
I am working with data from a video chat app. Some entries are duplicated because they correspond to different events within a single call. The data looks as follows:
account_id | call_id | event | duration |
42 | abcdef | CallStarted | 22 |
42 | abcdef | CallEnded | 22 |
45 | abcdef | CallStarted | 21 |
45 | abcdef | CallEnded | 21 |
99 | ghijklm | CallReceived | 13 |
99 | ghijklm | CallAnswered | 13 |
99 | ghijklm | CallEnded | 13 |
13 | nopqrs | CallConnected | 39 |
13 | tuvwxy | CallAccepted | 15 |
13 | tuvwxy | CallEnded | 15 |
I need to get the sum of `duration` for each `account_id` in the table. However, I need to account for only one row per `account_id` and `call_id` combination.
I would like to get the following result from the sample data I posted above:
account_id | sum_duration |
42 | 22 |
45 | 21 |
99 | 13 |
13 | 44 |
How can I achieve this?
Solved! Go to Solution.
@Anonymous Try this:
Measure =
VAR __Table =
GROUPBY(
'Table',
[account_id],
[call_id],
"__Sum",SUMX(CURRENTGROUP(),[duration])
)
VAR __Result = SUMX(__Table,[__Sum])
RETURN
__Result
@Anonymous Try this:
Measure =
VAR __Table =
GROUPBY(
'Table',
[account_id],
[call_id],
"__Sum",SUMX(CURRENTGROUP(),[duration])
)
VAR __Result = SUMX(__Table,[__Sum])
RETURN
__Result
In the end, I opted for importing the data using `ROW_NUMBER()` directly in the SQL query to only sum the first row per combination.
However, your solution works too, so thank you!
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 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |