Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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.

HarishKM
Impactful Individual
Impactful Individual

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

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

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

Have I solved your problem?
Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;).
chrome-9xf-Zagzel-B

If you found this post helpful, please give Kudos.
It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen.
If you find my signature vaguely amusing, please give Kudos.
KIfp67uy-Sr
Proud to be a Super User!PBI-Super-User-Rank-30x30-1x
v-yiruan-msft
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
Super User

@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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.