topic Re: Date Hierarchy shows Blanks in Visual in DAX Commands and Tips

Date Hierarchy shows Blanks in Visual

SteveMBSDO — Fri, 24 Feb 2023 08:59:14 GMT

Hello,

When I want to visualize [Duration] on a Date x-axis the visual doesn’t recognize the date hierarchy and I get (Blank) instead. See picture. How can I solve this to make it look like the last picture attached? I think I'm missing something basic...

I created a Date Table using the following DAX:

This is the format of the original date in the FACT Table:

This is how I’ve set up the relationship between the [Date Table] and [Reporting Date]

When I just use [Duration] and [Reporting Date] from the same table I get the result I want, but I want to use the dimension table because I have to add new tables later on.

Thank you!

Re: Date Hierarchy shows Blanks in Visual

andhiii079845 — Fri, 24 Feb 2023 09:05:01 GMT

Do you have perhaps a .pbix file?

Re: Date Hierarchy shows Blanks in Visual

SteveMBSDO — Fri, 24 Feb 2023 09:07:58 GMT

Yes, but how can I upload it to here?

Re: Date Hierarchy shows Blanks in Visual

andhiii079845 — Fri, 24 Feb 2023 09:12:29 GMT

I see the problem in your screenshot: dimDate is DD-MM-YYYY 00:00:00 but in your FACT table: DD-MM-YYYY 06:00:00. They will not join via the relationsship.

You have to separate date and time in the fact and dim table.

Re: Date Hierarchy shows Blanks in Visual

SteveMBSDO — Fri, 24 Feb 2023 09:15:29 GMT

Awesome thank you! I've adjusted the 6:00 to 0:00 in Power Query and now they match!

Re: Date Hierarchy shows Blanks in Visual

andhiii079845 — Fri, 24 Feb 2023 09:17:45 GMT

One advice. If you not need the time than separate it from the the date. One column for date, one for time. But fine that it works. 🙂