Field is a date in the Power Query Editor, but no date hierarchy showing up in the Report View?

[Anonymous]

Hi community - I'm struggling to understand why Power BI isn't recognizing a field (AuditDate) as a Date hierarchy in the report view, yet is able to recognize the same field as a date in the Power Query Editor. As a comparision, I'm included similar screenshots of a field (d_calendar) which is being recognized both in the report view and PQE as a date:

Both are a Date data type in Power Query Editor:

Yet only one of these (DAY_DATE) is a date hierarchy in the Power BI report view:

I think this is causing me issues with utilizing DAY_DATE as the date in report view (dimensional modeling), even though the two tables are apparently linked in the data model:

At a loss of what to try. D_CALENDAR is SQL Server data, while the AuditQuestionResponses is SharePoint data, for what it's worth. Any ideas?

[bcdobbs · Accepted solution]

Hi,

The moment you create a relationship between two date fields the date field on the many side ( your audit date) becomes a foreign key. Power Bi then assumes you wish to disable the auto datetime table it normally creates in the background and use your own date table instead (D_Calendar) in this case.

This is generally a much a better way to go in terms of dimensional modelling. It does however mean that you need to create the date heirachy in your date table. The following link may get you started: Creating a simple date table in DAX - SQLBI

Alternatively if you delete the relationship the auto date hierarchy will return.

[Anonymous]

Makes sense on disabling the auto datetime to use the date table instead - TIL, thanks!

The date heiarchy already exists in my date table (DAY_DATE), so it must be some other issue in my data that is causing blank visuals in my report. I'll go debug other items - appreciate it!

EDIT - after disabling the relationship, the audit date was in fact changed to a date heirarchy automatically. After that, I reenabled the relationship between the two tables and everything else worked correctly (slicing, etc.). Order of operations, or something. 👍