Forum Discussion
Helper IApply Custom Date Hierarchy to Model
I'm working on my first Power BI Project and am consistently running into issues as I learn the system.
I have a series of entries which I'm trying to break down by their dates, weekly, which of course is not normally a date hierarchy option (seriously, why?!). So, I've created a custom Date table within the project using DAX which includes the WEEKNUM column, and created the hierarchy, then trying to build the relationship to connect it but I must be doing that part wrong because once the relationship is applied the hierarchy (and entire "Date" classification, for that matter) for the "Open Date" column I'm trying to work with all disappear.
I've tried variants of the relationship cardinality but none seem to work - they either report circular dependancies or "work" with the date functionality removed. I'm sure it's something fairly simple I'm missing but I'm genuinely stuck here...
I'll try to give Kudos accordingly to all those who have helped but ultimately a combination of fixes were necessary:
1. Ensure there exists a Date table within the model that includes its own hierarchy with "Week" within it.
2. Separate the table of data into 2 tables, one for opened items and one for closed items
3. Ensure each table's Date column is either formatted as "Date" (rather than DateTime) or else create a duplicate column that is defined as Date only
4. Connect the two tables' "Date" formatted Date columns to the Date table within the model
5. Build the Visualisation, using the Count of the date only date columns of the two data tables as the column values, and the Date hierarchy as the shared axis - the data has come out perfectly.
12 Replies
- jdbuchanan71
Super User
I think you are having trouble becuase the [Open Date] column in your 'Problems in the last 12 weeks data' table is a date/time. It is not able to find a matching entry in the Date column because of the time portion of the filed. You can convert it to just the date using INT in a new column.
Open Date Only = INT ( 'Problems in the last 12 weeks data'[Open Date] )Then join that into your date table.
- PantherusNZ
Hi, Where would I enter that bit of code?
I tried creating the date-specific column in data transforming to get everything aligned but it still refuses to acknowledge that connection.
- jdbuchanan71
In the table view you would add it as a new column.
Hi PantherusNZ ,
The date hierarchies will not work when there is one relationship(with multiple cardinality) on that field. Please review the content in the following links and check whether the methods in these links can help you resolve your problem.
Auto Date Hierarchies Gone after Adding Table/Join
Best Regards
- PantherusNZ
Hello, thank you for your reply and links.
I've read through those discussions but don't understand how to apply the advice to what I'm trying to do. I had created the new Date table because I wanted to be able to add Week to the hierarchy, with it in place, even when marked as the date table, the hierarchies still are only Year/Quarter/Month/Day - then if I apply the relationship to try and actually connect them the hierarchy vanishes. So clearly I'm doing it wrong but I don't understand from those links what to actually do...
- HarishKM
PantherusNZ Create a common key to connect try using this dax
Key = datevalue(date[date)
then do it for table now create a relationship in between both table
output will be like this .
- PantherusNZ
Hi, Thank you for your reply.
Apologies for being a bit slow here but I'm still learning - would I be raising that key reference for the new Date table or the table that I'm working with? If the latter, there are two separate columns containing date values that I'm trying to work with, would I have to do this for both?
Also, where would I enter that code line? Does it matter?
- HarishKM
PantherusNZ Hey Right just do it with your both table date table and fact table .
at later stages if you want to create another relationship with diffrent date then Userelationship
fulfill your requirement.
Kindly refer above Link for more info.
- PantherusNZ
I'll try to give Kudos accordingly to all those who have helped but ultimately a combination of fixes were necessary:
1. Ensure there exists a Date table within the model that includes its own hierarchy with "Week" within it.
2. Separate the table of data into 2 tables, one for opened items and one for closed items
3. Ensure each table's Date column is either formatted as "Date" (rather than DateTime) or else create a duplicate column that is defined as Date only
4. Connect the two tables' "Date" formatted Date columns to the Date table within the model
5. Build the Visualisation, using the Count of the date only date columns of the two data tables as the column values, and the Date hierarchy as the shared axis - the data has come out perfectly.
