Forum Discussion
Custom Date Hierarchy and Hierarchy Labels
I am trying to get my PBI Desktop models more efficient and one tip I found was to disable Auto Date/Time Tables. https://blog.crossjoin.co.uk/2016/12/16/power-bi-model-size-bloat-and-auto-datetime-tables/
This means that you have to create your own hierarchy which is kind of OK. The problem is that these custom hierarchies don't have hierarchy labels to make them compatible with time intelligence functions like, for example, if I have a Date hierarchy and I want to sumarise by month, I can use the following measure with an automatically generated Date hierarchy (note the dot notation to access the hierarchy member)
salesPrevMonth3 :=
CALCULATE(
[sales],
PARALLELPERIOD('Dim Date'[_Date].[Date], -1, MONTH)
)
This works fine in a visual at any level of drill down. For example, if the row context is months, it will show current and previous month total sales, if the row context is days, it will show the total for the previous month against every day (not "expected behaviour" but, at least it's something).
If I'm using a visual with a date hierarch row context then this does not work
salesPrevMonth3 :=
CALCULATE(
[sales],
PARALLELPERIOD('Dim Date'[_Date], -1, MONTH)
)Because the measure chokes on the hierarchy.
But, when I disable Auto Date/Time Tables and build my own hierarchy, I lose the hierarchy labels feature (the dot notation) and can no longer use hierarchies in visuals with time intelligence functions.
Is this just a todo feature or is there a way to get this working with custom hierarchies?
=> I want to dissable Options/DataLoad/Time intelligence/Auto Date/Time because that is the advice of the link in my original post because it will make the model run faster. I then want to create my own Date Hierarchy (as advised in the link), and I want to know if there is a way to use the dot notation on the Hierarchy I create.
What I'm talking about is also the Options/DataLoad/Time intelligence/Auto Date/Time. And what I mean is that when you disable this option, the dot notation like 'Calendar Table'[Date].[Date] or 'Calendar Table'[Date].[Day] or others can no longer be used. This is by design.
So, no. You cannot use the dot notation if you disable the Options/DataLoad/Time intelligence/Auto Date/Time.
=> If this is also disabling the dot notation then it should say so, otherwise it is confusing or it is a side effect.
Yes, as I know Power BI didn't tell us this feature. But it is simple to make a test and find out.
=> It is not disabling "Time Intelligence" as you put it.
I'm always talking about the Options/DataLoad/Time intelligence/Auto Date/Time not the Time Intelligence functions in DAX. If I have confused you on this point, sorry about that.
=> In fact, it seems that this option has nothing to do with the dot notation in Custom Hierarchy so, I just want to clarify that dot notation is not implemented for Custom Hierarchy. Correct?
Sure. Dot notation has nothing to do with Custom Hierarchy. The dot notation is just used to get the date part of dates like year, month... There's many other ways to get these date parts.
Hope this is clear for you.
Thanks,
Xi Jin.
6 Replies
I am trying to get my PBI Desktop models more efficient and one tip I found was to disable Auto Date/Time Tables.
This means that you have to create your own hierarchy which is kind of OK. The problem is that these custom hierarchies don't have hierarchy labels to make them compatible with time intelligence functions like, for example, if I have a Date hierarchy and I want to sumarise by month, I can use the following measure with an automatically generated Date hierarchy (note the dot notation to access the hierarchy member)
firstPrevMonth = CALCULATE( FIRSTDATE(Invoiced[Date]), PREVIOUSMONTH('Dim Date'[Date].[Date]) )This works fine in a visual at any level of drill down. For example, if the row context is months, it will show current and previous month total sales, if the row context is days, it will show the total for the previous month against every day (not "expected behaviour" but, at least it's something).
If I'm using a visual with a date hierarch row context then this does not work…
firstPrevMonth = CALCULATE( FIRSTDATE(Invoiced[Date]), PREVIOUSMONTH('Dim Date'[Date]) )Because the measure chokes on the hierarchy.
But, when I disable Auto Date/Time Tables and build my own hierarchy, I lose the hierarchy labels feature (the dot notation) and can no longer use hierarchies in visuals with time intelligence functions.
Is this just a todo feature or is there a way to get this working with custom hierarchies?
=> when I disable Auto Date/Time Tables and build my own hierarchy, I lose the hierarchy labels feature (the dot notation) and can no longer use hierarchies in visuals with time intelligence functions.Is this just a todo feature or is there a way to get this working with custom hierarchies?
Yes, it is. As you can see in this article: https://powerbi.microsoft.com/en-us/blog/power-bi-desktop-april-update-feature-summary/
You can now use DAX time intelligence functions with the built-in time intelligence functions. With the Inline hierarchy labels preview feature turned on, you can access these fields with a dot notation (e.g., Sales[InvoiceDate].[Date]). The dot notation works in all places a normal date field can be used.
Thereby when you disable the Time intelligence, the dot notation cannot be used.
Thanks,
Xi Jin.Hi, sorry, I don't follow your meaning.
The link you provided was for a 2016 version and that option has long since disappeared.
I have no idea about the concept of "built-in" vs DAX time intelligence functions, can you please explain that?
Finally, my question is not about the in-line hierarchy labels working with automatic hierarchies, I am clear on how that works. My question is about if you create your own hierarchy. I think you are saying that this cannot be accessed with dot notation but it's not really clear.
