Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
Anonymous
Not applicable

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 Table2)  by a DAX formula. 

 

Table1: Currently my table looks like this in PowerBI

 

ShivaniRao_3-1654097548447.png

 

Table2: But my table is expected to look like this

 

ShivaniRao_2-1654097523453.png

 

 

Scenario Explained Below:

 

I have used SQL as a datasource and used Direct Query for connecting to a table in powerbi. In Table1, I have a Column called "Object Name" which runs in a "Hourly"/"Daily" basis in a day. Lets take if a Object runs Hourly in a day then there should be 24 entries for a single object in a day. If it is a Daily run then there should be 1 entry for that object in a day. 

 

Instead of 24 entries, i am getting 72entries for a hourly job in a day which is getting multiplied by 3. Simily for daily run i am getting 3 entries where i should get only 1 entry.

 

For eg:

1. If it is a Hourly run:

then for object "AAA" there should be 24entries in a day which starts from 12:00:00 AM and ends at 23:59:00 PM.

So it will be like 12:00:00 AM to 01:00:00 AM, then 01:00:00 AM to 02:00:00 AM, then 02:00:00 AM to 03:00:00 AM .......till 23:59:00PM.

2. If it is a Daily run:

then for object "AAA" there should be only 1 entry in a day which starts from 12:00:00 AM and ends at sometime.

For eg: if it is "Daily 6am" then it will start at daily 6am in a day.

 

object starts running in this way:

 

There are three layers, the object first starts running from Layer1, then from Layer2 and then from Layer3 which is explained in detail below:

First it starts from Layer1 to Layer2 which captures the overall timetaken in "Duratoin1" column and
then from Layer2 to Layer3 which captures the overall timetaken in "Duratoin2" column and
then from Layer3 to Layer4 which captures the overall timetaken in "Duratoin3" column.

 

Currently all these 3 durations are captured in 3 different rows , but i want it in same row like Table2.

 

Thanks.

1 ACCEPTED SOLUTION

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:

SergioSilvaPT_0-1654766980185.png

 

and the result:

SergioSilvaPT_1-1654767250332.png

 

 

Regards,

Sérgio Silva 

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

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

Check out my blog for tips and tricks about Power BI: https://pbibits.wordpress.com/

View solution in original post

5 REPLIES 5
Anonymous
Not applicable

@SergioSilvaPT 

 

Many thanks for your solution. But i want the minimum start time and maximum endtime for every hour if it is a Hourly frequency.

 

Please refer the below table which has minimum start time and maximun endtime for every hour for ObjectName "AAA".

ShivaniRao_0-1654170996677.png

 

And i have one more query....should i create the below measure as 3 seperate measures for all the 3 duration columns (Duration1,Duration2, Duration3)....????

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

 

 

Thanks.

 

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:

SergioSilvaPT_0-1654766980185.png

 

and the result:

SergioSilvaPT_1-1654767250332.png

 

 

Regards,

Sérgio Silva 

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

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

Check out my blog for tips and tricks about Power BI: https://pbibits.wordpress.com/
SergioSilvaPT
Resolver V
Resolver V

Hi @Anonymous,

 

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

 

SergioSilvaPT_0-1654100744419.png

 

SergioSilvaPT_1-1654100754963.png

 

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.

 

Regards,
Sérgio Silva

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

Check out my blog for tips and tricks about Power BI: https://pbibits.wordpress.com/
Anonymous
Not applicable

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.

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

 

Regards,
Sérgio Silva

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

Check out my blog for tips and tricks about Power BI: https://pbibits.wordpress.com/

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.