Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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!
User | Count |
---|---|
77 | |
76 | |
44 | |
31 | |
26 |
User | Count |
---|---|
97 | |
89 | |
52 | |
48 | |
46 |