cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Frequent Visitor

Merge rows contain different time values

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?

 

 

IDAgent NameTime in Seconds
85Ira7200
85Ira144000
85Ira14000
85Ira2400
85Ira40000
85Ira23000
85Ira14000
288Isha63
288Isha63
288Isha63
288Isha86400
288Isha86400
288Isha86400
1 ACCEPTED SOLUTION
Anonymous
Not applicable

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

 

Image 1596.png

 

TableName = 

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

 

View solution in original post

5 REPLIES 5
Anonymous
Not applicable

From Modeling create a New Table as follow:

TableName = 

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

 

Image 1595.png 

 

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

Anonymous
Not applicable

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

 

Image 1596.png

 

TableName = 

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

 

View solution in original post

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
Not applicable

@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.

 

Image 1597.png

Resolver I
Resolver I

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.

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Wave Release 2

Check out the updates in Power BI.

Overview of Power BI 2020 release wave 2!

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors