Date hierarchies not working in DirectQuery Mode

[Anonymous]

Date hierarchies aren't working in DirectQuery Mode, but are working in Import mode. Please help

Import Mode

DirectQuery Mode

[sandermatthijs]

Searching around, I think below is the best response. Turning on Date/Time Table does NOT fix this in DirectQuery Mode. You will need to request the Model owner to add a Date Table (and name the bloody fields appropriately so users KNOW the date table pllies to Column X, so don't call it Date Table, call it "Purchased Date Table"!)

In Power BI, the built-in date hierarchy feature is not available when using DirectQuery mode. This is because DirectQuery relies on real-time data retrieval from the source, and the automatic date hierarchy functionality is typically handled during data import and transformation. 

Here's why it's not available and how to address it:

Why it's not available:

• DirectQuery's Real-time Focus:

  DirectQuery fetches data directly from the source, and the automatic date hierarchy is not part of that real-time data retrieval process. 
• Import Mode vs. DirectQuery:

  The automatic date hierarchy is a feature designed for imported data where Power BI can analyze and structure the data during the import process. 

How to work around it:

1. 1. Create a custom date hierarchy:

   • In your data source (or within Power BI using Power Query), create separate columns for Year, Quarter, Month, and Day. 
   • Optionally, create a new hierarchy in Power BI by right-clicking the date column and selecting "New Hierarchy". 
   • Drag the individual year, quarter, month, and day columns into the new hierarchy. 
2. 2. Use a DAX time intelligence function:

   • If your data source has a date table, you can use DAX (Data Analysis Expressions) functions to create a calculated table or measure that handles the date hierarchy. 
3. 3. Create a separate date table:

   • Import a date table (if one doesn't already exist in your data source). 
   • Create a relationship between the date table and your DirectQuery table. 
   • Use the date table's hierarchy in your visualizations. 

Key considerations:

• Performance:

  When creating custom hierarchies, be mindful of performance, especially with large datasets.
• Data Source Limitations:

  The specific steps may vary depending on your data source and its capabilities. 

By implementing these workarounds, you can effectively manage date hierarchies within your DirectQuery models in Power BI. 

[amitchandak]

@Anonymous ,there can be a few reasons.

You might have switched off auto date time at file level (In options and Setting -> Options )

Joined these dates with a date table

These are the reasons Date Hierarchy can be missing
https://community.powerbi.com/t5/Desktop/Date-Hierarchy-Doesn-t-show/td-p/525460
https://community.powerbi.com/t5/Desktop/Date-hierarchy-not-available/td-p/438804
https://community.powerbi.com/t5/Desktop/Lost-Missing-Date-Hierarchy/td-p/421045

Check Settings
https://docs.microsoft.com/en-us/power-bi/transform-model/desktop-auto-date-time

I always prefer a date table and not the auto date time hierarchy