Date hierarchy drill down labels

[Surm]

Hello,

What is the recommended (or correct) way to formulate a "date hierarchy" so that drill down labels appear and behave reasonably?

Date hierarchy is on X-axis. I created two date hierarchies as below (on a line chart)

Hierarchy 1 Calendar Year (Type: whole number, Format: YYYY) Calendar Month Year (Type: text, Format: MMM YYYY) Date (Type: date) Drill down: via the middle button Used on Page 2 of the attached PBIX

Hierarchy 2 Calendar Year (Type: whole number, Format: YYYY) Calendar Month (Type: text, Format: MMM) Day of Month (Type: whole number) Drill down: via the right-most button Used on Page 3 of the attached PBIX

IMPORTANT: Fact Rain has data gaps.

No data from 2020-01-10 to 2020-01-20

No data from 2020-03-01 to 2020-05-20

Page 2

Now let's see how to plot this data on a line chart.

X-axis: Hierarchy-1

Y-axis: sum(Rain)   --just a simple measure

This is how the line chat appears when the X-axis is at "Year" level. This is as expected.

Q1.1) When drilled-down is done through "go to the next level in hierarchy" button, Month-Year labels appear slanted. 

Is there a setting to make them vertical? 

Worse, March 2020 and April 2020 are missing from the X-axis.
X-axis is "categorical" at "Month-Year" and that is probably why these categories (months) do not appear. Is there a way to get these months back? One may suggest to spit out "zero" in the measure for these two months, however, that is wrong because the the line will go down to zero for these two months. (No data does not mean "zero"). What should happen is the line should continue from Feb 2020 to May 2020 while keeping Mar 2020 and Apr 2020 in place. So, does it mean that using "text" members in a Date hierarchy is not the correct approach? I belive this problem can be fixed by setting the data type of Month-Year hierarchy member to  "date" but is formatted to show just the "year and month". 

Q1.2) When drilled-down is done through "go to the next level in hierarchy" button again, day and month labels disappear. I think this is because the date member of the hierarchy is of "date" type. I undertand that we cannot fit all date labels invidually, however, whenever a label is shown, can the full label (year month date) be shown rather than just the "year" part?

(I understand that if we want all individual dates to appear on X-axis, we can change X axis to "Categorical", however, then the dates that have no values will be excluded from X-axis which is not misleading for a date axis. If select "Show items with no data", it will include all dates, not just in betweens, from the Date dimension which is also not desirable)

Page 3

Q2.1) When drilled-down is done through the "Expand all down one level in the hierarchy" button, Year and Month labels appear be slanted. Can these be aligned vertically?

Mar 2020 and Apr 2020 are missing from the X-axis, due X-axis being categorical. So, is the correct design principle to have "date" typed columns to hold Year/Month/Date hierarchy members? 
 

[Surm2]

Hi @BinaryBotany , 
Tagging you to see whether you have any opinions, suggestions, workarounds etc

[BinaryBotany]

Hi Surm,

On X axis label formatting:

Q1.1/2.1

There is no way I am aware of to control the slanting of text other than resizing the visual, or changing whether the x label concatenates the labels or not. Other than moving to Tableau, which handles this sort of customisation a lot better.

Using the "expand all down one level" on page 3 (YY/MM/DD heirachy) keeps the previous context, so we don't lose any information, and it allows us to unselect "concatete labels", which, IMO, provides a better looking axis. 

The problem is, your end uses can never use the "go to the next level in the heirachy", as it will be misleading (you'll end up with Jan from 2020 and 2021 being grouped together into the Jan category.

Q1.2

Your understanding of the situation is correct. Because you go down to the next level of the heirachy without keeping the context of the previous level, it takes the date and converts the axis to continuous. Unfortuantely, again, you have no control over the X axis labels.

On missing data when using categorical axes:

Q1.2 cont.

Yes, using "show items with no data" on a categorical axis means you get ALL the dates in the date dim (without the values going to 0). So you need to then put a filter on the visualisation to filter the date dim to only the relevant dates. The most basic form is hard coding the dates to start and end on, though sometimes you may want this to be more dynamic.

Overall, dealing with datetime based data in PowerBI is frustrating, so this isn't a very satisfactory answer sorry! I'll be interested to see how you go formatting the year and month fields as dates, and whether that will give you the continuous axis at all heirachy levels you are looking for 🙂

[BinaryBotany]

@Surm2 

TLDR: don't mark the date dimension as a date table! This will solve all your problems described here.

-----------------

This actually came up for me today, so I tested out the method of making each layer of the date heirachy a date and formatting it as year/month/day. It didn't work.

I think the problem here is having a date dimension and "marking it as a date table". A formal date table doesn't appear to allow continuous date heirachies for some reason. 

I don't know heaps about "mark as date table" -- infact, I've never had a use case where it was needed. But I wonder if the solution here is to have two date tables. One is a "mark as date table" (if you need it), and the other is the actual date dimension. That way, when you need a continuous axis with data being pulled from multiple fact tables, use the date in the date dimension. When you need to do some DAX that requires a "mark as date table", use the other table. 

Seems pretty inefficient. For now, I will be continue to ignore the option to "mark as date table" where ever possible.

[Surm2]

I am attaching a Onedrive link to download the *.pbix and Excel data file

https://1drv.ms/u/s!AnmrXDyD1CyDkXsS8igW7S01rqqp?e=cbBh4Z