Forum Discussion

souhail98's avatar
souhail98
Frequent Visitor
5 years ago
Solved

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 (...
  • Anonymous's avatar
    Anonymous
    5 years ago

    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