Fixed X-Axis interval?

[icbd]

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.

[selimovd]

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:

https://softcrylic.com/blogs/power-bi-for-beginners-how-to-create-a-date-table-in-power-bi/

And here a Power Query code for a time table:

https://raw.githubusercontent.com/selimovd/PowerBISnippets/main/PowerQuery_M/Time table with buckets...

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

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[icbd]

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 · Accepted solution]

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]

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