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.

     

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.