cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
PantherusNZ
Helper I
Helper I

Apply 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...

 

PantherusNZ_0-1620096630136.png

PantherusNZ_1-1620096674263.png

PantherusNZ_2-1620096711151.png

 

 

 

1 ACCEPTED SOLUTION
PantherusNZ
Helper I
Helper I

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.

View solution in original post

12 REPLIES 12
PantherusNZ
Helper I
Helper I

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.

View solution in original post

HarishKM
Responsive Resident
Responsive Resident

@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 .

 

HarishKM_0-1620298328772.png

 

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
Responsive Resident
Responsive Resident

@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.

Hi,

 

OK so I've been hunting around the application and finally guessed where to enter the key line of code by creating a measure under the newly created Date table OR in the data table - but it is throwing errors every time I try, whether I try to refer it to its own Date column or the specific Open Date Only column in the data table.

 

PantherusNZ_0-1620618948491.pngPantherusNZ_1-1620618974071.pngPantherusNZ_2-1620619090384.png

 

Apologies for being a bit slow but I don't quite understand where I should be entering these commands since I'm clearly doing something wrong...

KNP
Responsive Resident
Responsive Resident

Hi @PantherusNZ

 

Personally, I would have done the data modelling portion of this (including the date table) in Power Query, not DAX. 

 

Unless you need the timestamp as a single column, there can be significant benefit in splitting the date and time into seperate columns to reduce high cardinality, especially in large data models.

This would also serve to give you suitable join columns as your date table will typically only go down to 'date' at the lowest grain and your newly split column is ready to join to your date dimension.

 

If you want to continue with the DAX version, I believe @HarishKM is referring to 'New Column' not 'New Measure'.

 

Hope this helps.

 

Regards,

Kim

yingyinr
Community Support
Community Support

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.

Date hierarchy missing

Auto Date Hierarchies Gone after Adding Table/Join

Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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...

jdbuchanan71
Super User II
Super User II

@PantherusNZ 

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.

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.

In the table view you would add it as a new column.

jdbuchanan71_0-1620100144495.png

 

Aha!  OK, thank you - created the column.

 

I've recreated the Relationship to the custom date table and...no hierarchy.

Helpful resources

Announcements
MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Top Solution Authors
Top Kudoed Authors