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 inter...
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.

     

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

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.