Forum Discussion
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
Table2: But my table is expected to look like this
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.
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.
5 Replies
- SergioSilvaPT
Resolver V
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.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
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.
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".
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.
- SergioSilvaPT
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.