Forum Discussion
Row data in chronological order
Hi,
The table below is my datasource, every row is an activity within the Transport ride (1st column left) . I dont know how to convert the vertical data to horizontal in a chronological order (start, load, unload, end) based on time.
| Ride header | activity id | activity name | Time | zip code, etc |
| 313790 | 180978 | start | 06:00 | 4844 |
| 313790 | 180979 | Load | 06:15 | 4844 |
| 313790 | 180980 | unload | 13:15 | 5001 |
| 313790 | 180981 | end | 17:00 | 4844 |
I want to make a table visual with calcultated (duration) columns, something like this below:
| Ride header | Start | load | Duration load time | Zipcode | unload | Duration Drive and unload time | Zipcode | end | Duration drive back time | Total time |
| 313790 | 06:00 | 06:15 | 15 min | 4844 | 13:15 | 7 hours | 5001 | 17:00 | 3:45 | 11 hours |
I hope it is understandable.
Thanks in advance.
Hi souhail98 ,
I created a sample pbix file (see attachment) base on provided data, please check whether that is what you want.
1. Create a Sort table
2. Create a measure as below to get the duration
Duration = VAR _curtime = SELECTEDVALUE ( 'Table'[Time] ) VAR _preaid = CALCULATE ( MAX ( 'Table'[activity id] ), FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Ride header] = SELECTEDVALUE ( 'Table'[Ride header] ) && 'Table'[Time] < _curtime ) ) VAR _pretime = CALCULATE ( MAX ( 'Table'[Time] ), FILTER ( ALLSELECTED ( 'Table' ), 'Table'[activity id] = _preaid ) ) VAR _duration = DATEDIFF ( _pretime, _curtime, SECOND ) //var _day= INT(_duration/(24*60*60)) VAR _hour = MOD ( INT ( _duration / ( 60 * 60 ) ), 24 ) VAR _minute = MOD ( INT ( _duration / 60 ), 60 ) VAR _second = MOD ( _duration, 60 ) RETURN //if(_day=0,BLANK(),_day&"day(s) ")& IF ( _hour = 0, BLANK (), _hour & "hour(s) " ) & IF ( _minute = 0, BLANK (), _minute & "minute(s) " ) & IF ( _second = 0, BLANK (), _second & "second(s) " )3. Put the field activity name of Sort table(Sort by column: Order ) , field Ride header and measure onto matrix
Sort a Column with a Custom Order in Power BI
Best Regards
3 Replies
Hi souhail98 ,
I created a sample pbix file (see attachment) base on provided data, please check whether that is what you want.
1. Create a Sort table
2. Create a measure as below to get the duration
Duration = VAR _curtime = SELECTEDVALUE ( 'Table'[Time] ) VAR _preaid = CALCULATE ( MAX ( 'Table'[activity id] ), FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Ride header] = SELECTEDVALUE ( 'Table'[Ride header] ) && 'Table'[Time] < _curtime ) ) VAR _pretime = CALCULATE ( MAX ( 'Table'[Time] ), FILTER ( ALLSELECTED ( 'Table' ), 'Table'[activity id] = _preaid ) ) VAR _duration = DATEDIFF ( _pretime, _curtime, SECOND ) //var _day= INT(_duration/(24*60*60)) VAR _hour = MOD ( INT ( _duration / ( 60 * 60 ) ), 24 ) VAR _minute = MOD ( INT ( _duration / 60 ), 60 ) VAR _second = MOD ( _duration, 60 ) RETURN //if(_day=0,BLANK(),_day&"day(s) ")& IF ( _hour = 0, BLANK (), _hour & "hour(s) " ) & IF ( _minute = 0, BLANK (), _minute & "minute(s) " ) & IF ( _second = 0, BLANK (), _second & "second(s) " )3. Put the field activity name of Sort table(Sort by column: Order ) , field Ride header and measure onto matrix
Sort a Column with a Custom Order in Power BI
Best Regards
Hi Anonymous
Thank you very much! The activities needed to be on chronological order based on time. The example below shows multiple activities within one ''rit header''. The activities are performed interchangeably within the process. Is it possible to make a dynamic process timeline?
Best regards,
Souhail
Hi souhail98 ,
In this case, there is no need to create another Sort dimension table. First select field activity name, and then navigate to Column tools ribbon and click Sort by column to select Time field.
Best Regards
