Missing Date Hierachy Lakehouse Model

[Martin_Ber]

Hello everyone,

I’m currently working on creating a central date dimension table in Microsoft Fabric (Dataflow Gen 2) to filter and aggregate several fact tables. Although I’ve followed the guidelines from Microsoft documentation the automatic date hierarchy (like the one available in Power BI Desktop) is not working as expected. Furthermore I tried to do it by myself, but it isn´t working well. 

Here’s what I’ve done so far:

• Created a date table using Power Query, including the columns: Day, MonthNo, Month, QuarterNo, Quarter, and Year.
• Set the Date column as type date and ensured that the table contains no gaps or duplicates in the date range.
• Marked the table as a "Date Table" in Fabric and selected the Date column as the primary date field. I also tried it without that marking. 

Despite this, I cannot achieve the functionality where I can automatically aggregate or filter data using a hierarchy (e.g., Year > Quarter > Month) for example drill up or down functions. Of course I can do that with numeric values but I want that monthnames. If I add this in my table, power bi sorting it by alphabet. 

Questions:

1. Is there a specific column format required (e.g., should Month or Quarter also be stored as a date type instead of text)?
2. Are there additional steps needed in Fabric to enable automatic hierarchies similar to Power BI Desktop?
3. Is this functionality even supported in Fabric at this time?

situation i need (german)

Situation now

Thank you in advance for your help.

[v-achippa]

Hi @Martin_Ber,

Thank you for reaching out to Microsoft Fabric Community.

Thank you @nilendraFabric and @pallavi_r  for addressing the issue.

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided by the super user for the issue worked? or let us know if you need any further assistance?
If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

Thanks and regards,

Anjan Kumar Chippa

[pallavi_r · Accepted solution]

Hi @Martin_Ber 

We can achieve the hierarchy in the semantic model in power bi service.

1.First I created a date dimension sql table, added custom column for quarter name using concatenate and datepart function, and added that to the semantic model. Power Query is also fine.

2.Open Data Model, go to date dimension. Create a date hierarchy as shown below

3.Ensure qtrname text type column is sorted by qtr column 

4. Step no 3 - repeat for month as well

5.Hierarchy is ready to use in visualization now. Only drag the column needed for visual as its already sorted in semantic model

6.Here you go

If this post helps, please accept this as a solution. Appreciate your kudos.

Thanks,

Pallavi

[nilendraFabric]

Hi @Martin_Ber 

• The automatic date hierarchy feature in Power BI Desktop relies on the Auto Date/Time option. 
• However, this feature is not always supported in all scenarios or environments, such as DirectQuery mode or certain configurations in Fabric

This is a great arcticle discussing same problem in detail 

https://community.fabric.microsoft.com/t5/Power-BI-Community-Blog/Why-there-is-no-date-hierarchy/ba-...

Please see if this helps and accept the answer if this is helpful

Thanks