Date Hierarchy in Direct Query

[Chandu_G]

I noticed that the date hierarchy feature is not available for DirectQuery. This feature is incredibly useful, as it allows for the consolidation of metrics based on the hierarchy, such as by month or year. This capability is particularly beneficial for forecasting, as it enables us to make projections based on different time intervals within the hierarchy.

[Anonymous · Accepted solution]

Hi @Chandu_G 

You are correct that, traditionally, the date hierarchy feature is not available in DirectQuery mode in Microsoft Power BI. The reason for this limitation is because DirectQuery mode is designed to query data directly from the underlying data source in real-time, rather than importing and processing the data within the Power BI model.

However, you can still achieve similar functionality by creating a date table and using DAX to create a custom date hierarchy within your DirectQuery model by using the "Enter Data" feature or by using Power Query to generate a date table that spans the necessary range of dates.

Or using calculated table to create a calendar table with Dax.

Then you can create a relationship between the calendar table and the DirectQuery mode table, then you can use the date hierarchy feature.

Best Regards

Zhengdong Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.