Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Timeline or replacement

Hi all, 

 

I'm looking for a timeline option in PowerBI. To define timeline a bit clearer: 

I need to show in a report the different states (the state of the account) of an employee during the day. The states are generated and saved with timestamps (starttime, endtime and duration as HH:MM:SS). 

 

Is there a way to created a detailed timeline (which can handle minutes, or even seconds, as a meassure) in PowerBI? A.t.m. I'm trying to create a solution with R and intergrate it with PowerBI, but due to my lack in R knowledge this is going slowly. 

 

Regards, 

 

André

Tips and Tricks
  • stretcharm's avatar
    stretcharm
    8 years ago

    Yes you can do many things with the code. It simply builds up a string based on your rules.

    So it can change the Characters based on the status.

     

    So you can have severval variables for the different Statuses

    VAR __FULLCHARState1 = 9679 -- State1
    VAR __FULLCHARState2 = 128523 -- State2
    VAR __FULLCHARState3 = 128546 -- State3

     

    Then you can conditionally Set _FULLCHAR or even build the string using a mixture of Characters

    The return basically repeates the blank Char to get to the start then the required char for the duration. However it can be adapted to do anything you want.

     

     

    IF(
        NOT ISBLANK(__BASE_VALUE),
            REPT(UNICHAR(__BLANKCHAR), __START_RATING) &
        REPT(UNICHAR(__FULLCHAR), __BASE_RATING)              
    )

     

    Its a variation on cwebb Star Rating Dax http://community.powerbi.com/t5/Quick-Measures-Gallery/Star-Ratings/m-p/166903

    He's also got a links to a blog post with other variations.

     

    Play around with the Expressions. If you download my example it has a number of examples and a data set to play with.

     

     

    GanttDot =
VAR __MAX_NUMBER_OF_BLOCKS = 100
VAR __MIN_RATED_VALUE = 0  
VAR __STARTBASE_VALUE = MIN(Durations[StartTime])
VAR __BASE_VALUE = Sum(Durations[Minutes])
VAR __MAX_RATED_VALUE =  CALCULATE(MAX(Durations[EndTime]), ALL(Durations))
-- Monospace characters
VAR __BLANKCHAR = 32 -- Space
VAR __FULLCHAR = 9679 -- Dot
VAR __NORMALIZED_STARTBASE_VALUE =
    MIN( MAX(
        DIVIDE(
            __STARTBASE_VALUE - __MIN_RATED_VALUE,
            __MAX_RATED_VALUE - __MIN_RATED_VALUE
        ), 0 ), 1 )
VAR __NORMALIZED_BASE_VALUE =
    MIN( MAX(
        DIVIDE(
            __BASE_VALUE - __MIN_RATED_VALUE,
            __MAX_RATED_VALUE - __MIN_RATED_VALUE
        ), 0 ), 1 )
VAR __START_RATING = ROUND(__NORMALIZED_STARTBASE_VALUE * __MAX_NUMBER_OF_BLOCKS, 0)
VAR __BASE_RATING = MAX(ROUND(__NORMALIZED_BASE_VALUE * __MAX_NUMBER_OF_BLOCKS, 0),1)
RETURN
    IF(
        NOT ISBLANK(__BASE_VALUE),
            REPT(UNICHAR(__BLANKCHAR), __START_RATING) &
        REPT(UNICHAR(__FULLCHAR), __BASE_RATING)              
    )

     

5 Replies

  • stretcharm's avatar
    stretcharm
    Memorable Member
    8 years ago

    Do you have an idea what you want it to look like?

     

    You might get some ideas from my SSIS Dashboard. I was looking for a time based Gantt chart and tried some in R but ended up create a dax text version. I've also got some other time based charts, though I tend to sumerised by either hour or 15 min counters. These let me plot onto a scatter chart.

    http://community.powerbi.com/t5/Data-Stories-Gallery/SSIS-Catalog-DB-Dashboard/m-p/244677#M1110

    http://community.powerbi.com/t5/Quick-Measures-Gallery/Text-Gantt-Chart/m-p/253466/highlight/true#M57

     

    I like the new Ribbon chart for showing change over time.

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      Hi stretcharm,

       

      Thank you for you response, that looks impressive! 

      It would be great to be able to create a text gantt chart for my problem. But is it able to replace the tasks with a "status", which don't repeat in the list? E.g. we use the status of skype (Online, Busy, Away, Offline), would it be possible only use 4 rows, and change the "dots" of the gantt chart between rows? Sorry if it is cryptic, not a native English speaker :P 

      • stretcharm's avatar
        stretcharm
        Memorable Member
        8 years ago

        Yes you can do many things with the code. It simply builds up a string based on your rules.

        So it can change the Characters based on the status.

         

        So you can have severval variables for the different Statuses

        VAR __FULLCHARState1 = 9679 -- State1
        VAR __FULLCHARState2 = 128523 -- State2
        VAR __FULLCHARState3 = 128546 -- State3

         

        Then you can conditionally Set _FULLCHAR or even build the string using a mixture of Characters

        The return basically repeates the blank Char to get to the start then the required char for the duration. However it can be adapted to do anything you want.

         

         

        IF(
        NOT ISBLANK(__BASE_VALUE),
            REPT(UNICHAR(__BLANKCHAR), __START_RATING) &
        REPT(UNICHAR(__FULLCHAR), __BASE_RATING)              
    )

         

        Its a variation on cwebb Star Rating Dax http://community.powerbi.com/t5/Quick-Measures-Gallery/Star-Ratings/m-p/166903

        He's also got a links to a blog post with other variations.

         

        Play around with the Expressions. If you download my example it has a number of examples and a data set to play with.

         

         

        GanttDot =
VAR __MAX_NUMBER_OF_BLOCKS = 100
VAR __MIN_RATED_VALUE = 0  
VAR __STARTBASE_VALUE = MIN(Durations[StartTime])
VAR __BASE_VALUE = Sum(Durations[Minutes])
VAR __MAX_RATED_VALUE =  CALCULATE(MAX(Durations[EndTime]), ALL(Durations))
-- Monospace characters
VAR __BLANKCHAR = 32 -- Space
VAR __FULLCHAR = 9679 -- Dot
VAR __NORMALIZED_STARTBASE_VALUE =
    MIN( MAX(
        DIVIDE(
            __STARTBASE_VALUE - __MIN_RATED_VALUE,
            __MAX_RATED_VALUE - __MIN_RATED_VALUE
        ), 0 ), 1 )
VAR __NORMALIZED_BASE_VALUE =
    MIN( MAX(
        DIVIDE(
            __BASE_VALUE - __MIN_RATED_VALUE,
            __MAX_RATED_VALUE - __MIN_RATED_VALUE
        ), 0 ), 1 )
VAR __START_RATING = ROUND(__NORMALIZED_STARTBASE_VALUE * __MAX_NUMBER_OF_BLOCKS, 0)
VAR __BASE_RATING = MAX(ROUND(__NORMALIZED_BASE_VALUE * __MAX_NUMBER_OF_BLOCKS, 0),1)
RETURN
    IF(
        NOT ISBLANK(__BASE_VALUE),
            REPT(UNICHAR(__BLANKCHAR), __START_RATING) &
        REPT(UNICHAR(__FULLCHAR), __BASE_RATING)              
    )