Create a measure to sum unique values from another column and group by ID

[arturos]

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?

[Greg_Deckler · Accepted solution]

@arturos Try this:

Measure = 
  VAR __Table = 
    GROUPBY(
      'Table',
      [account_id],
      [call_id],
      "__Sum",SUMX(CURRENTGROUP(),[duration])
    )
  VAR __Result = SUMX(__Table,[__Sum])
RETURN
  __Result

[arturos]

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!