cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
gavin007
Helper IV
Helper IV

Date hierarchy missing in powerbi dataset

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

before.PNG

 

 

After it is used as direct query from Pbi Dataset

 

after.PNG

 

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.  

Capture.PNG

 

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?

4 ACCEPTED SOLUTIONS
amitchandak
Super User IV
Super User IV

@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

 



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

v-jingzhang
Community Support
Community Support

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.

View solution in original post

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

View solution in original post

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.

View solution in original post

8 REPLIES 8
v-jingzhang
Community Support
Community Support

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.

View solution in original post

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?

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.

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.

@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?

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

View solution in original post

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.

View solution in original post

amitchandak
Super User IV
Super User IV

@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

 



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

April Update

Check it Out!

Click here to read more about the April 2021 Updates!

MBAS Carousel

Sign up for our May 4th event!

May the fourth be with you, join us online!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.