Why there is no date hierarchy

[v-yiruan-msft]

Scenario:

Do you often see a calendar icon next to the date field? Yes, this is the date hierarchy of Power BI. You can easily identify it because it's adorned with a calendar icon.

What is the meaning of the calendar icon? We use it a lot, but we may not be clear about the meaning of the icon. Here is the official statement:

Calendar date field with a built-in date table.

Using this option can be convenient when creating ad hoc models or performing data exploration or profiling. But sometimes when you need the date hierarchy to draw a graph, and you find that there is no calendar icon next to the field. why the date hierarchy disappeared? This is too bad, and I can't proceed to the next step. I need the date hierarchy to help me. 

It is clear that the date field type in the model is already a date type. Why is there still no date hierarchy? If you have encountered such a problem, then this blog is written for you.

Auto date/time

First of all, we need to know that the date hierarchy is due to the automatic date and time feature.

The Auto date/time is a data load option in Power BI Desktop. The purpose of this option is to support convenient time intelligence reporting based on date columns loaded into a model. Specifically, it allows report authors to use your data model to filter, group, and drill down by using calendar time periods (years, quarters, months, and days)

If you have not enabled this option, then you need to enable this option first.

In Power BI Desktop, you select File > Options and settings > Options, and then select either the Global or Current File page. On either page, the option exists in the Time intelligence section.

Auto date/time can be configured globally or for the current file. The global option applies to new Power BI Desktop files, and it can be turned on or off at any time. For a new installation of Power BI Desktop, both options default to on.

The current file option can also be turned on or off at any time. When turned on, auto date/time tables are created. When turned off, any auto date/time tables are removed from the model.

Caution:

Take care when you turn the current file option off, as this will remove the auto date/time tables. Be sure to fix any broken report filters or visuals that had been configured to use them.

 

How it works

Each auto date/time table is in fact a calculated table that generates rows of data by using the DAX CALENDAR function. Each table also includes six calculated columns: Day, MonthNo, Month, QuarterNo, Quarter, and Year.

If it were possible to see the rows of an auto date/time table, they would look like this:

Considerations:

• Calendar periods only: The year and quarter columns relate to calendar periods. It means that the year begins on January 1 and finishes on December 31. There's no ability to customize the year commencement (or completion) date.
• Model size: For each date column that generates a hidden auto date/time table, it will result in an increased model size and also extend the data refresh time.

When the option is enabled, Power BI Desktop creates a hidden auto date/time table for each date column, providing all of the following conditions are true:

• The table storage mode is Import
• The column data type is date or date/time

For more information about the auto date/time option, please check the official documentation:  Apply auto date/time in Power BI Desktop

Still no date hierarchy?

When we turn on this option and meet the prerequisites, but we still can’t see the date hierarchy. Why?

Let's see the example below.

There are two tables, Date and Table. The Date column is a date type, the Key column is an integer type, and the other columns are a text type. It looks like this:

This is the case with a date hierarchy, and there is no relationship between the tables.

The next step is to establish the relationship between the two tables for the dates.

We see that in Figure 1 and Figure 2, the many side of the one-to-many relationship will lose the date hierarchy, while one side will retain the hierarchy.

In Figure 3 and Figure 4, when the relationship between dates is one-to-one or many-to-many, why is there such a result?

How does Power BI determine which one is truly Many in a one-to-many relationship? And Power BI will remove the date hierarchy for this field.

To find out the answer to this question, let's look at the GIF image below.

From the above we can see that when establishing the relationship between fields, the first selected field is regarded as Many by Power BI, and the second selected field is regarded as One by Power BI.

At this point we finally understand how Figure 3 and Figure 4 work.

And we also noticed that other date fields that are not directly related will retain the date hierarchy. So when we use the Date Key field (integer type) to establish a relationship, the date type column will maintain the date hierarchy.

When a date field is related to another non-date field (such as an integer type Date Key field). Power BI still removes the hierarchy of fields on Many side of a one-to-many relationship.

When the relationship is one-to-one or many-to-many, Power BI will still determine which field is truly Many based on the order of selection as mentioned above.

 

Conclusion

Okay, according to what we have seen above, another important condition that affects the date hierarchy:

• The column isn't the "many" side of a model relationship

When the relationship is one-to-many, Power BI will respect your decision and remove the "many" side date hierarchy.

When the relationship is one-to-one or one-to-many, Power BI will determine which one is truly "many" side according to the order of selection.

Now let's see what we need to be aware here regarding the date hierarchy.

• Enable automatic date and time
• The table storage mode is Import
• The column data type is date or date/time
• The column isn't the "many" side of a model relationship

When the above conditions are met, the date hierarchy will work for you.

Mark as date table

You think that's all there is to it? No, there is actually another situation where you still can't see the date hierarchy even if you meet the above conditions.

Figure 10, here is the normal case mentioned above, i.e., the date hierarchy is preserved at one end.

But we notice that in Figure 11, even if all the conditions above are met, there is still no date hierarchy here. And next to the date is a new icon.

What is the meaning of this icon?

Identity field: Fields with this icon are unique fields, set to show all values, even if they have duplicates. For example, your data might have records for two different people named 'Robin Smith', and each will be treated as unique. They won't be summed.

Want to know all the icons?

Using the Field list in Power BI Desktop

The reason why the date hierarchy will still not be visible is the table is marked as a date table.

To set a date table select the table you want to use as a date table in the Fields pane, then right-click the table and select Mark as date table > Mark as date table in the menu that appears. You can also select the table and then select Mark as Date Table from the Table tools ribbon, as shown in the following image.

It's important to note that when you specify your own date table, Power BI Desktop does not auto-create the hierarchies that it would otherwise build into your model on your behalf. If you later deselect your date table (and no longer have a manually set date table), Power BI Desktop recreates the automatically created built-in date tables for you, for the date columns in the table.

Also important to note is that when you mark a table as a date table, the built-in (automatically created) date table that Power BI Desktop created is removed, and any visuals or DAX expressions you previously created based on those built-in tables will no longer work properly.

For more information about the date table, please see the documentation:

Setting your own date table

Recommendations

Because each date column produces its own (hidden) auto date/time table, it's not possible to apply a time filter to one table and have it propagate to multiple model tables. Filtering in this way is a common modeling requirement when reporting on multiple subjects (fact-type tables) like sales and sales budget. When using auto date/time, the report author will need to apply filters to each different date column.

When your data source already defines a date dimension table, this table should be used to consistently define time within your organization. It will certainly be the case if your data source is a data warehouse. Otherwise, you can generate date tables in your model by using the DAX CALENDAR or CALENDARAUTO functions.

You can then add calculated columns to support the known time filtering and grouping requirements. This design approach may allow you to create a single date table that propagates to all fact-type tables, possibly resulting a single table to apply time filters. For further information on creating date tables, read the Set and use date tables in Power BI Desktop article.

End

Now we finally know that in addition to the previous condition, the table being marked as a date table also causes the fields to lose their date hierarchy.

That's all the content shared in this blog, hope it's useful for you.

REFERENCE  

https://docs.microsoft.com/en-us/power-bi/guidance/model-date-tables 

https://docs.microsoft.com/en-us/power-bi/transform-model/desktop-date-tables#setting-your-own-date-... 

https://docs.microsoft.com/en-us/power-bi/transform-model/desktop-field-list#field-list-icons 

Author: Zeon Zheng

Reviewer: Kerry Wang & Ula Huang