Date Hierarchy falls to appear when joining on the date

[pelowski]

I'm not sure if this is a bug or if we're doing something incorrectly, but we've had a heck of a time trying to figure out why a few dates weren't showing up as hierarchies when we bring them into a report. When we have a date in our query, we try to join it on a DimDate table that has a lot more information about the Date including things like the Day Name, the Week Number, etc.  However, when joining on this Date, PowerBI no longer allows it to have a hierarchy when we bring it into a report.

These are the things we've looked at...

1. Both dates are formatted as dates.
2. The joins work regardless of which field we use.
3. When we try to join on just the date (rather than the integer representation of that date) the hierarchy does not appear.

We've had to add this to all of our dates instead and then join on this field.

CAST(CONVERT(VARCHAR(8),OrdDetShipDate,112) AS INT) AS ShipDateInt,

We also looked at this thread while trying to find an answer to this, but this appears to be different.  We're using "Import" for our query.

https://community.powerbi.com/t5/Desktop/Date-hiearchy-does-not-work-automatically-in-case-of-Direct...

[MFelix · Accepted solution]

Hi @pelowski,

When you have a date Power BI automatically generate different views for different time hierarchies however when, you have a connection between tables (using date format) what happens is that the main date (table in wich you have the one in one-to-many relationship) is the one that get the hierarchy "treatament", so in your example when you need to use the Date in Calendar and not the Ship Date on your visuals.

I would discard the DateKey (int) and use the Dates to link between the two tables and use the date field on your calendar.

Regards,

MFelix