Merge rows contain different time values

[vsinne]

Hello,

I have this database where all the agents are filling their time as and when they perform any task in the given ID #. This is resulting in duplication of ID# as shown below and their unique Time.

1) How can i sum up individual IDs time and merge the duplicate rows?

2) Their are other rows that has the same time but still showing duplicate IDs. How can i merge them as well without summing up the timings?

ID	Agent Name	Time in Seconds
85	Ira	7200
85	Ira	144000
85	Ira	14000
85	Ira	2400
85	Ira	40000
85	Ira	23000
85	Ira	14000
288	Isha	63
288	Isha	63
288	Isha	63
288	Isha	86400
288	Isha	86400
288	Isha	86400

[Anonymous]

From Modeling create a New Table as follow:

TableName = 

    SUMMARIZE (
        sourceTable,
        sourceTable[ID],
        sourceTable[Agent Name],
       "Total Time", SUM(sourceTable[Time in Seconds])
    )

 

Let me know if I didn't undertood your requirements correctly.

[Anonymous · Accepted solution]

Or are you trying to get the following results @vsinne?

TableName = 

    SUMMARIZE (
        sourceTable,
        sourceTable[ID],
        sourceTable[Agent Name],
       "Total Time", SUMX(DISTINCT(sourceTable),sourceTable[Time in Seconds] )
    )

[vsinne]

Yes, this is the result i am expecting to yield. how can i merge those rows that has just the duplicate values? thank you so much for your help.

[Anonymous]

@vsinne, in Data view click on (1)create a New Table from the Modeling ribbon then (2)use the DAX formula I provided on my previous post.

[MarcoRotta]

I'm assuming you want to sum the times and keep only a single record of the other fields, and you want to do that in your data model. If so, starting from the big combined table you already have, try to use the "Group by" option in the "Tranform" menu inside query editor. That would generate the output you require.

Regards.