Date hierarchy missing in powerbi dataset

[gavin007]

I am using composite model for my data model. Part of the dataset is from PowerBi dataset, which has a date table I built from a Power Query.

The datatable works perfectly when I use it in the data model. But when I use it as direct query from PowerBi dataset, the date column hierarchy is missing completely. This create drama as I have to manaually create hierarchy again.

Before in its original data model

After it is used as direct query from Pbi Dataset

I tried to work around it with two options.

1. "Mark as date table" for the date table. this is not working as it change the date column to below format, losing all the date hierarchy completed in the original data model.  

2. In the Direct Query dataset, I manually date hierarchy. this is working but it means everytime I use direct query for the Pbi dateset, i have to manaually do it, let alone all the date sorting has been messed up. This is inelegant at all.

Any suggestion with this direct query date hierarchy missing issue?

[amitchandak · Accepted solution]

@gavin007 , If you want to mark the Table as a Date table do not expect a date hierarchy. better you create your own columns and use them. Date table needs the date as key column.

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

Better

create a new hierarchy
https://www.tutorialgateway.org/create-hierarchy-in-power-bi/
https://www.youtube.com/watch?v=x6vXVJZ_eTY

[v-jingzhang · Accepted solution]

Hi @gavin007 

The date hierarchy is generated by the auto date/time feature in Power BI Desktop. It only applies in Import models or import tables in Composite models in Power BI Desktop. Since your dataset is in DirectQuery mode, it will not show the date hierarchy. 

In your scenario, I would suggest to take your first option Mark as date table and create your own hierarchy columns in the date table. Although losing all the auto date hierarchy, you can use your custom calendar quarter, month, day columns in visuals and measures. I see you have already included these calendar columns in your date table, so feel free to use them as they will work the same as the date hierarchy. Then publish this dataset into Power BI Service and use it in DirectQuery mode.

Actually, when you have a date hierarchy column, PBI generates a built-in date table in your model. Although you don't see its structure but it does exist and occupies the data size in your model. Each date hierarchy column means a separate built-in date table in the model and this will make your model larger invisibly. Remove them will reduce your model size. This is a good practice.

Reference: 

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

How and Why you should mark a date table in Power BI

Kindly let me know if this helps.
Community Support Team _ Jing Zhang
If this post helps, please consider Accept it as the solution to help other members find it.

[v-jingzhang · Accepted solution]

Hi @gavin007 

To my knowledge, a good of Marking as date table is to reduce the data size of the model as it will remove the built-in date tables in Power BI. But since you will publish it to service and then connect to this dataset in DirectQuery mode, it seems this action doesn't mean a lot.

In option 2, there is a workaround to deal with the sorting problem. You can have both Fiscal Month Number column and Month Text column in a date table, then sort Month Text column by Fiscal Month Number column. Please refer to this similar topic: How to get month name instead of number - Microsoft Power BI Community

Regards,
Community Support Team _ Jing

[v-jingzhang · Accepted solution]

Hi @gavin007 

The date hierarchy is generated by the auto date/time feature in Power BI Desktop. It only applies in Import models or import tables in Composite models in Power BI Desktop. Since your dataset is in DirectQuery mode, it will not show the date hierarchy. 

In your scenario, I would suggest to take your first option Mark as date table and create your own hierarchy columns in the date table. Although losing all the auto date hierarchy, you can use your custom calendar quarter, month, day columns in visuals and measures. I see you have already included these calendar columns in your date table, so feel free to use them as they will work the same as the date hierarchy. Then publish this dataset into Power BI Service and use it in DirectQuery mode.

Actually, when you have a date hierarchy column, PBI generates a built-in date table in your model. Although you don't see its structure but it does exist and occupies the data size in your model. Each date hierarchy column means a separate built-in date table in the model and this will make your model larger invisibly. Remove them will reduce your model size. This is a good practice.

Reference: 

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

How and Why you should mark a date table in Power BI

Kindly let me know if this helps.
Community Support Team _ Jing Zhang
If this post helps, please consider Accept it as the solution to help other members find it.

[goflse]

Hi!
You say that you can create hierarchy columns in the table, buit is it possible to create an actual hiererchy like it's possible in SSDT. If so, how could yo do that?

[v-jingzhang]

Sorry, what do you mean by "create an actual hiererchy like it's possible in SSDT"? I don't know much about that in SSDT.

[goflse]

I found the solution. You create hierarcvhies mdifferently in SSDT and in Power BI Desktop. First I thought the possibility of creating manual hierarchies was not available in Power BI Desktop.

[gavin007]

@v-jingzhang I have done some research, it is the limit of DQ no doubt.

I have further question on those two options.

1. Mark as date table will loss all the inherent date hierarchy in the original date table, the only good about it is when 

• If the relationship between Sales and Date is based on a column of a data type other than Date, then you must mark the table as a date table to obtain the expected behavior from time intelligence calculations

Quote from: 

https://www.sqlbi.com/articles/mark-as-date-table/#:~:text=Tabular%20models%20(including%20Power%20B....

In my case, it is not the necessary as i will be not using the other column other than date column to connect to other tables. So again, what is the real benefit to use this option?

2. In option 2 when i manaully set up the hierarchy, there is a sorting problem. for example, with the inherant date hierarchy, Jan, Feb and Mar is sorted without a problem. but in the manual hierarchy, I create column call Month as text type in date table, when I include it in hierarchy, it will be sorted alphabetical.

The workaround is to cretate a Month column as number in date table and use it in hierarchy. The only problem is user may confuse with what the number means. I.e. in a Jul to Jun fiscal year, 1 means July or Jan.

So any better work around on this?

[v-jingzhang · Accepted solution]

Hi @gavin007 

To my knowledge, a good of Marking as date table is to reduce the data size of the model as it will remove the built-in date tables in Power BI. But since you will publish it to service and then connect to this dataset in DirectQuery mode, it seems this action doesn't mean a lot.

In option 2, there is a workaround to deal with the sorting problem. You can have both Fiscal Month Number column and Month Text column in a date table, then sort Month Text column by Fiscal Month Number column. Please refer to this similar topic: How to get month name instead of number - Microsoft Power BI Community

Regards,
Community Support Team _ Jing

[gavin007 · Accepted solution]

Thank all of the support first of all. I think the benefit of DQ outweigh the limit of it. Hopefully i can resolve the other issue along the way.

The sorting column by fiscal month is mind-blowing. I don't know this trick exist until today. Thanks Jing.

[amitchandak · Accepted solution]

@gavin007 , If you want to mark the Table as a Date table do not expect a date hierarchy. better you create your own columns and use them. Date table needs the date as key column.

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

Better

create a new hierarchy
https://www.tutorialgateway.org/create-hierarchy-in-power-bi/
https://www.youtube.com/watch?v=x6vXVJZ_eTY