If you’re struggling to analyze time-based data in Power BI, the date hierarchy feature is the ultimate tool to organize the data based on date and time.
While visualizing a report in Power BI, the date hierarchy options are missing for inappropriate actions such as incorrect relationships or date column format.
Throughout this article, I will discuss the reasons and demonstrate multiple methods to resolve the issue.
Without further ado, let’s begin!
Why is There no Date Hierarchy in Power BI?
Power BI won’t show date hierarchy if the Auto date/time option is disabled from the Power BI Options. Moreover, you won’t see the option when the date column is formatted as text type, the table is not marked as a date table, or the relationship between tables is inappropriate.
If the date field does not show hierarchy while creating a relationship, the Power BI Date Hierarchy won’t appear. To understand more, consider the subsequent discussion.
Here are the reasons why the Power BI date Hierarchy disappeared in Power BI:
Date Column is Formatted as Text Type
When the date column is formatted as a text type, Power BI fails to recognize the column as a date column. As a result, the Power BI date is not showing as a date, and the option is missing.
Date Table is Not Marked as a Date Table
If the report has a separate date table and is not marked as a date table, the date hierarchy will not show up in Power BI.
This issue arises because Power BI fails to recognize the date table correctly when the table is not marked as a date table.
Auto date/time Option is Disabled
Power BI offers an automatic date and time feature with the Date Hierarchy option. This feature allows users to filter, group, and sort data based on calendar time periods.
When the Auto date/time for new files option is disabled, Power BI cannot generate time intelligence reporting according to the column.
Inaccurate Relationship between Date Tables
Another common reason that restricts Power BI from accessing the date hierarchy is an inaccurate relationship between a date table and a fact table that contains a date column.
Whatever restricts it from showing up in Power BI can be fixed with simple and easy-to-follow methods. To resolve the issue, move down to the next section.
How to Fix Date Hierarchy Not Showing in Power BI
To fix the date hierarchy not appearing issue of Power BI, format the date column as a date type, mark the table as a date table, create an error-free relationship between the tables, or use a custom date hierarchy. Besides, enable the Auto date/time option from the Power BI Options.
While creating the relationships, you need to be more cautious because if you choose the wrong order, Power BI will fail to show the option.
Here are the methods to bring back the date hierarchy in Power BI:
1. Format the Date Column as Date Type
First thing first, if the column is formatted as text strings, you must convert it to date type. There are several methods of converting a text string to a date type.
You can format the date column using the FORMAT or the DATE function. Let me demonstrate how to convert using the versatile FORMAT function.
In the Power BI Desktop app, execute the following syntax to format the column as a date type.
FORMAT([DateColumn], "MM/DD/YYYY")Replace the DateColumn expression with the appropriate column name and hit Enter. When the date column is formatted as the date type, jump to the following method.
2. Mark the Table as a Date Table
Though Power BI Desktop works behind to identify date columns, it sometimes fails due to different data types in the tables.
In such scenarios, you can manually mark the table as a date table by performing the subsequent instructions.
- Select the table you need to mark as a date table from the Fields pane.
- Right-click on the selected table and choose Mark as date table.
- Select Mark as date table from the context menu.
Alternatively, you can accomplish the same using the Table tools from the top ribbon. Simply navigate to the Table tools tab and choose Mark as date table >> Mark as date table.
However, if you find the “Mark as date table” option missing, ensure the table has a unique identifier for each date and the Auto date/time option is turned on. Move to the next method to learn how to enable the Auto date/time option.
3. Turn On the Auto date/time Option
The automatic date and time feature of Power BI is the reason why the date hierarchy is available. When the Auto date/time option is turned off, Power BI cannot generate a date hierarchy.
Here’s how to enable the Auto date/time option:
- Launch the Power BI Desktop application.
- Click on the File from the ribbon.
- Navigate to Options and settings >> Options.
- Locate Time intelligence in the Data Load tab under Global.
- Tick the Auto date/time for new files option.
- Click OK to save changes.
Once you enable the Auto date/time option, Power BI will show the option.
However, if the problem persists, go to the next method to bring back the calendar or date hierarchy icon.
4. Create a Relationship between Date Tables
While building a relationship between two tables, the correct order of selection is very important. If you create relationships in the wrong order, Power BI may fail to identify the date hierarchy correctly.
If you create a one-to-many relationship where a date field is related to a non-date field, Power BI usually moves the date hierarchy to the Many side.
However, if you create the relationship in the correct order, Power BI will easily identify which field is Many and display the option under the correct field.
5. Use Custom Date Hierarchies
As a last resort, you can create and use a custom date hierarchy. To do so, right-click on the relevant field and choose New Hierarchy from the context menu. Set a name for the newly created hierarchy and set this hierarchy field to the desired order.
Alternatively, using the CALENDAR and CALENDARAUTO functions, you can create a new hierarchy to perform time-based calculations in Power BI.
Wrap Up
If you cannot find the Date Hierarchy option or the option is missing while visualizing your date table, ensure the Auto date/time option is turned on.
Besides, ensure the date column is formatted as a date type, and the table is marked as a date table. Also, make an error-free relationship between the two tables to get the option.
If you’ve further queries regarding this issue, comment below.
