Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
DanielM16
Frequent Visitor

Date Hierarchy in DirecyQuery mode on a Date/Time column

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?

1 ACCEPTED SOLUTION
v-shex-msft
Community Support
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

3 REPLIES 3
v-shex-msft
Community Support
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
lbendlin
Super User
Super User

" 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?

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.