Forum Discussion
Date heirarchy labels missing from visualization
I'm building my first data model, and I've run into my first major roadblock, the date heirarchy doesn't show any labels on the table. This is true regardless of the measure I select, or if I select the date heirarchy or a the simple date column.
I've imported a "Ticket data" table, and created a separate rolling "Calendar" table.
Calendar = ADDCOLUMNS(
CALENDAR(DATE(2021,1,1),TODAY()),
"Year", YEAR([Date]),
"Quarter", "Q" & QUARTER([Date]),
"Month", FORMAT([Date], "MMMM"),
"Month Number", MONTH([Date]),
"Day Name", FORMAT([Date], "dddd"),
"Day Number", DAY([Date]),
"Start of Year", DATE(YEAR([Date]),1,1),
"Start of Quarter", DATE(YEAR([Date]), ROUNDUP(MONTH([Date])/3, 0)*3-2, 1),
"Start of Month", DATE(YEAR([Date]), MONTH([Date]), 1),
"Start of Week", [Date] - WEEKDAY([Date], 2) + 1
)
I've confirmed the appropriate columns are marked as "Date/time" data types in both the Calendar and Ticket data tables.
The Calendar table itself is marked as the Date table; and I have an active 1:M relationship between 'Calendar'[Date] and 'Ticket data'[Created date] with a single cross filter direction.
I've refreshed the data multiple times.
I am stumped.
Hello, oftenconfused ,
this will sound silly, but double check that the Created Date is set as DATE data type in Power Query (often it can be set to Datetime, or nothing/any so watch for that)
6 Replies
Hello, oftenconfused ,
this will sound silly, but double check that the Created Date is set as DATE data type in Power Query (often it can be set to Datetime, or nothing/any so watch for that)
At first, I didn't see you said "Power Query" when I went back and corrected it there, that worked! Thank you so much. I appreciate you.
oftenconfused glad it helped.
When adding the Calendar[Date] field to a table or chart, Power BI should automatically create a date hierarchy (Year, Quarter, Month, Day).
If you don’t see it, right-click the date field in the Fields pane and check if “Date Hierarchy” is available.** You don’t need to create a relationship with the Date Hierarchy itself only between Calendar[Date] and Ticket data[Created date].
Try recreating the relationship by dragging Calendar[Date] to Ticket data[Created date] in Model View.
Let me know if this helps! If this response was helpful, please accept it as a solution and give kudos to support other community members.
ArwaAldoud
Hi,when you use the calendar (and you have it also marked), this auto hierarchy is disabled. You can also disable it by default in settings, which is always the preferred way. Auto date tables are only causing performance issues.
In these kind of issues, the problem is usually around data types of the keys used to create the relationship, as it was in this case.
Thanks for the clarification about the hierarchy being disabled when using a marked calendar. vojtechsima Appreciate your insight.
