Forum Discussion
Date hierarchy drill down labels
Hi BinaryBotany ,
Tagging you to see whether you have any opinions, suggestions, workarounds etc
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 🙂
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.