Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Power BI Combine 3 rows into a single row

Hi,   I have table called Table1 which is shown below, I would be happy if you guide me how I can combine the 3 rows(Duration1, Duration2, Duration3) in Table1 into a single row (which is shown in ...
Need Help
Tips and Tricks
  • SergioSilvaPT's avatar
    SergioSilvaPT
    4 years ago

    ShivaniRaoo If you use the following measures you can achieve both Start and End time.

     

     

    Start_Time = CALCULATE( FORMAT( MIN( tbl[StartTime] ), hhh:mmsss" ))

End_Time = CALCULATE( FORMAT( MAX( tbl[StartTime] ), hhh:mmsss" ))

     

     

    The second question i didn't understand, if you want the total duration you can achieve it with the following measure:

     

     

    Total Duration = SUM('Table'[Durarion1]) + SUM('Table'[Duration2]) + SUM('Table'[Duration3])

     

     

    And you get something similar to this:

     

    Example data:

     

    and the result:

     

     

    Regards,

    Sérgio Silva 

     
    If this post helps, then please consider Accept it as the solution to help the others find it more quickly.
SergioSilvaPT's avatar
SergioSilvaPT
Icon for Resolver V rankResolver V
4 years ago

Hi Anonymous,

 

You can easy achieve that using Group by in Power Query.

 

 

 

You group by Name and then use the min, max and sum to achieve the other columns results.

 

Regards,

Sérgio Silva 

 
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi SergioSilvaPT ,

     

    I am using Direct Query mode while connecting to SQL db. In direct query mode it wont be possible for me to edit data in Power query editor. So it would be more helpful for me if this can be achieved using DAX.

     

    Thanks.

    • SergioSilvaPT's avatar
      SergioSilvaPT
      Icon for Resolver V rankResolver V
      4 years ago

      Anonymous for the values you can use the following measure:

       

       

      Duration_3 = 
CALCULATE(
    SUM(tbl[Dur3]),
    SELECTEDVALUE(tbl[Name]) = tbl[Name] && SELECTEDVALUE(tbl[Date]) =  tbl[Date]
)

       

      For the date if you want the max you can use this measure:

       

      Start_Time = CALCULATE( FORMAT( MAX( tbl[StartTime] ), "hh:mm:ss" ))

       

       

      Regards,

      Sérgio Silva 

       
      If this post helps, then please consider Accept it as the solution to help the others find it more quickly.