Date Hierarchy in DirecyQuery mode on a Date/Time column

[DanielM16]

I'm aware that date hierarchies are not working when a table is imported via DirectQuery. As a workaround, I usually use an auxiliary date table that I connect to my main table , but that doesn't seem to work when the column is of type Date/Time. Even when I change its type to Date, the relationship still doesn't work properly (without ever showing any kind of error message).

I know about the other common workaround, where the hierarchy is created manually via custom columns as outlined here, but considering that I have multiple date columns, that's not really a practical solution for me.

Is there any way I can get the auxiliary table approach to work with a Date/Time column?

[v-shex-msft · Accepted solution]

Hi @DanielM16,

AFAIK, normally power bi will build some hidden calendar table for each date times for quick analysis and time intelligence functions usage. (this feature does not include time fields due to performance reasons; if fully mapping time and date ranges, it will generate huge amount of records that will affect the performance and report usage) 

This feature not works when you used the 'live' mode data sources due to dynamic records.

Apply auto date/time in Power BI Desktop 

For this scenario, perhaps you can try to create a bridge table to extract and expand the raw table and different date fields. (bridge table structure: raw table key, date attribute, date field)
Then you can use the 'key' to link the raw table, the 'date' field to link calendar, the 'attribute' used to further control the filter accuracy.

Notice: I often use this when I faced a fact table with different date fields in import mode, I'm not so sure if this also fully work with 'live' mode data tables. 

Regards,

Xiaoxin Sheng

[lbendlin]

" imported via DirectQuery"  - good one 🙂

Havng multiple date columns does not preclude you from using an auxilary dates table. You can USERELATIONSHIP() to switch on the fly.

Will your report users slice the data by date dimension attributes (day/week/month etc) or do they explicitly need to be able to do time slicing?  Could you have a separate "Time of Day" reference table?

[DanielM16]

Time slicing won't be required, only day/week/month ...