Continuous X axis using Date and Time dimension

[BinaryBotany]

A simple example of what I am trying to achieve written below and found in the linked PBIX:

https://ufile.io/y2gjrz2n

https://drive.google.com/file/d/1tZkHBrwxVQXXHG3CuH9JXL2-DQ7GjHOY/view?usp=sharing

I am trying to create a line chart with a dual Y axis, pulling measures from two different fact tables. Both fact tables contain the measure and a datetime. I want datetime to be on a continuous X axis. I am unable to join the two fact tables, as they have different dimensions linking to them not represented here. I can achieve what I want by creating a DateTime dimension to link the two tables, and the result looks like this:

However, I need to present data from multiple years, with the datatime being recorded at the second granularity. Thus, the DateTime dimension gets very large, unweildy and slow.

Best practice dictates that I use a date and a time dimension, and link these two fact tables to the two dimensions using an appropiate date and time column derived from the datetime in the fact. The relationships are laid out below:

I can achieve a continuous X axis when I use just use a date from Date Dimension on the X axis. However, I don't want to aggregate the data to the date level. Likewise, I can achieve a continue axis when I just use the Time Dimension (which isn't useful).

However, when I try and create a DateTime X axis using both the date from the Date Dim and the time from the Time Dim using a heirarchy, it creates a categorical X axis.

How can I achieve a continuous DateTime X axis using a Date and Time dimension? 

[Anonymous]

I am stuck in a similar situation. Did you manage to find a workaround?

[BinaryBotany]

No, I did not. You have to build a datetime dimension unfortuntely. 

[WFM]

Is this what you are looking for?

[BinaryBotany]

Hi WFM,

Looks like you've just got time on the X-axis. I am looking for a datetime, with the date from a date dimension and time from the time dimension.

[Anonymous]

Hi, @BinaryBotany

Have you managed to figure out the solution? Btter than just having Date and time table merged into one datetime table that you can use as a workaround? 

[BinaryBotany]

Nope. Using a datetime dimension is still the best options unfortunately.

[parry2k]

@BinaryBotany it is surely one of those corner cases where concatenation is screwing things up. Good luck with whatever path you decided to go. Cheers!!

✨ Follow us on LinkedIn and  to our YouTube channel

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.

[parry2k]

@BinaryBotany I guess at this point if you really need date/time on the x-axis dimension, the only option, as you suggested, is to create a separate dimension that has date/time and use that on the x-axis, I don't see any other workaround. I wish I could suggest something better but looks like this is the only option at this point. 

[BinaryBotany]

Well, it's good to know that no one else is able to figure this out either.

Thank you for your help.

[parry2k]

@BinaryBotany I see what is going on. when you are using Expand All down in the hierarchy, it concatenates the x-axis and that's what makes it a text column (internal behavior) you don't see continuous but when you use go to the next level  in the hierarchy or turn on the drill on , it works fine. the following video explains the behavior. I hope it all makes sense.

✨ Follow us on LinkedIn and  to our YouTube channel

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.

[BinaryBotany]

Good observation. When using "Go to next level in the heirachy", it effectely moves between having the date on the axis, and the time on the axis, both which, by themselves, uses a continuous axis. Unfortunately, I need datetime on the axis.

Drill down doesn't work either -- it still converts the X axis to categorical.

As you say, the internal process is to join the date and the time into a string rather than a datetime when "expanding all down". So how can we work around this limitation? Is there some way we can create a datetime from the data and time dimensions that can be used on an axis? (other than creating a whole new datetime dimension table)

[parry2k]

@BinaryBotany I see. Get it. Can you share your pbix file using one drive/google drive? Remove sensitive information before sharing.

[BinaryBotany]

I have added an additional download link to the original post for a google drive download option:

https://drive.google.com/file/d/1tZkHBrwxVQXXHG3CuH9JXL2-DQ7GjHOY/view?usp=sharing

Will be interested to see if you have any better luck than me! I hope there's some smart DAX or M that will solve my problem.

[parry2k]

@BinaryBotany Can you confirm DateTime columns actual type in date time dimension is DateTime. I'm not sure why it would change to categorical. If you use the date time column directly on your visual, do you get continuous or categorical? I actually did a video on this link here, ofcourse it doesn't have a time element but it shows how you can year/month column as continuous.

✨ Follow us on LinkedIn and  to our YouTube channel

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.

[Anonymous]

Hi @parry2k ,
Thank you for your video on this topic. Nicely explained.

[BinaryBotany]

The datetime column in the datetimeDim is a datetime, and when used on the X asis along with the two fact measures, it works as expected (image 1 in the OP).

The issue comes when trying to recreate that same visual with separate date and time dimensions. Using date and time as a heirachy forces the X axis to a categorical axis (image 3 in OP, bottom visual). This is the challenge -- how to combine the date from dateDim and time from timeDim into a datetime that can be used on an X Axis to create a continuous axis.

[VijayP]

@BinaryBotany 

When you are getting smallest to largest sort , you can change the sort the way you want by clicking on Elipses ...

[BinaryBotany]

The categorical X-Axis is already sorted by time by default. Sort order doesn't change the fact that it's a categorical axis... not a continuous one, which is the problem I am trying to solve here.

[VijayP]

@BinaryBotany 
Try using IF(ISBLANK(measure),Blank(), Measure) which may cover only continuous values

[BinaryBotany]

I'm not sure what you are attempting to do here sorry, nor how it relates to combining a date and time from different tables into a continuous X dimension. Maybe you can elaborate?