Forum Discussion

icbd's avatar
icbd
Helper I
4 years ago
Solved

Fixed X-Axis interval?

Good day to all of you.

 

I am having trouble formating my X-Axis in a clustered bar chart to have a fixed interval. I am tracking a machine productivity each minute.

 

I would like a fixed interval so that whenever my production is 0 for a particular, it would show up as 0 on my bar chart.

 

This worked prior to adding the date + hour drilldowns. These were added because if I wanted to see more than a single day's values, they would get added together.

 

This is what I'm getting:

 

Pardon the scale as I just shoved this together, but this is what I want:

 

Any help would be appreciated, thank you.

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi icbd 

    I think you want to show a hierachy level like Year/Month/Day/Hour/Minute in your X axis.

    Here I suggest you to create a calendar table as below.

    Calendar =
GENERATE (
    GENERATE (
        ADDCOLUMNS (
            CALENDARAUTO (),
            "Year", YEAR ( [Date] ),
            "Month", MONTH ( [Date] ),
            "Day", DAY ( [Date] )
        ),
        GENERATESERIES ( 0, 23 )
    ),
    'Minute'
)

    Minute is a calculated table as well. 1-59

    Minute = GENERATESERIES(0,59,1)

    Then add a calculated column as a Keycolumn to create relationship with Data table.

    NewKey = 'Calendar'[Year]*100000000+'Calendar'[Month]*1000000+'Calendar'[Day]*10000+'Calendar'[Hour]*100+'Calendar'[Minute]

    Add a calculated New Key Table in Data table.

    NewKey = YEAR(Sheet10[Datetime])*100000000+MONTH(Sheet10[Datetime])*1000000+DAY(Sheet10[Datetime])*10000+HOUR(Sheet10[Datetime])*100+MINUTE(Sheet10[Datetime])

    Then create a relationship between two tables.

    Finally create a visual as below. You need to sort your table by X axis and turn off Concatenate labels in Format.

    Best Regards,
    Rico Zhou

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

4 Replies

  • selimovd's avatar
    selimovd
    Most Valuable Professional
    4 years ago

    Hey icbd ,

     

    you should create a proper date and time dimensional table and use them for the axis. Like this you will have the desired result.

    Be aware that you need a proper date table in order for the time intelligence functions to work. Here is a small tutorial how to create a date table:
     
    And here a Power Query code for a time table:
     
    If you need any help please let me know.
    If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍
     
    Best regards
    Denis
     
    • icbd's avatar
      icbd
      Helper I
      4 years ago

      I currently have a date table but not an Hour/Minute table.

    • icbd's avatar
      icbd
      Helper I
      4 years ago

      Good day selimovd.

       

      Your solution has gotten me part of the way there, I am now able to see the empty hours, but not the empty minutes yet. I'm not sure whether this would be a rounding error or not?

       

      I tried using Generateseries(0,23.99,1/60) to generate this but it did not work properly.

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        Hi icbd 

        I think you want to show a hierachy level like Year/Month/Day/Hour/Minute in your X axis.

        Here I suggest you to create a calendar table as below.

        Calendar =
GENERATE (
    GENERATE (
        ADDCOLUMNS (
            CALENDARAUTO (),
            "Year", YEAR ( [Date] ),
            "Month", MONTH ( [Date] ),
            "Day", DAY ( [Date] )
        ),
        GENERATESERIES ( 0, 23 )
    ),
    'Minute'
)

        Minute is a calculated table as well. 1-59

        Minute = GENERATESERIES(0,59,1)

        Then add a calculated column as a Keycolumn to create relationship with Data table.

        NewKey = 'Calendar'[Year]*100000000+'Calendar'[Month]*1000000+'Calendar'[Day]*10000+'Calendar'[Hour]*100+'Calendar'[Minute]

        Add a calculated New Key Table in Data table.

        NewKey = YEAR(Sheet10[Datetime])*100000000+MONTH(Sheet10[Datetime])*1000000+DAY(Sheet10[Datetime])*10000+HOUR(Sheet10[Datetime])*100+MINUTE(Sheet10[Datetime])

        Then create a relationship between two tables.

        Finally create a visual as below. You need to sort your table by X axis and turn off Concatenate labels in Format.

        Best Regards,
        Rico Zhou

         

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.