Row data in chronological order

[souhail98]

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.

[Anonymous · Accepted solution]

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

[souhail98]

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

[Anonymous]

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