The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredCompete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.
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!