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
ansa_naz
Continued Contributor
Continued Contributor

Cannot get date hierarchy to work on my date table for some reason?

Hi all

For some reason I cannot get PBI to recognise the date field as a date hierarchy in my date table. See below example of the data I am using:

 

https://1drv.ms/u/s!AuiIgc_S9J5JhbcesBfQnu7UDcHCCQ?e=RkhOPv

 

My data is:

 

No Date Hierarchy.jpg

 

For some reason the relationship between CCNoteDate and CCNotesGrouped is recognising as 1:1 even though it should recognise as 1:M. I know I can manually change it but this may be part of the reason why this issue is happening?

 

I need to add a bar chart, showing count of CCNotesGrouped.RefRecID by CCNoteDate.YearMonth2. Currently however all I get is:

 

Current bar chart.jpg

 

If anyone can help that would be marvellous!

Cheers

1 ACCEPTED SOLUTION
ansa_naz
Continued Contributor
Continued Contributor

I created a duplicate of the LatestDate field and changed it to Date type in Edit Query - this has fixed the issue

View solution in original post

6 REPLIES 6
v-diye-msft
Community Support
Community Support

Hi @ansa_naz ,

If the data type of the  column is date/time, it will never match a date in your date table even if the format is date. There are more digits there that you've simply hidden with the formatting. The data types should match.

 

Please change the format under query editor and try again.

 

 

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

Check the data type, by the looks of your fields pane it's not a date?

ansa_naz
Continued Contributor
Continued Contributor

Hi @jthomson its definitely a date, see screenshots:

 

CCNotesDate fieldsCCNotesDate fieldsCCNotesGrouped fieldsCCNotesGrouped fields

I have amended type for field CCNotesDate.CalendarDate from Date to Date/time, per comment from @v-diye-msft. So now both date fields are of type Date/Time

 

However, I still get the following bar chart:

 

Current bar chart1.jpg

 

Any further ideas?

Cheer for all help

Hi @ansa_naz ,

 

Please set both date/time to date in query editor. kindly back to me if still doesn't work. 

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

Hi @v-diye-msft 

I have changed both to Date in Query Editor just as a test. I cant keep it like that as I need CCNotesGrouped.LatestDate to be a date/time - I need the time portion.

 

GroupedDateType1.jpgLookupDate type1.jpg

So I changed both to Date, and I got a message that I cannot do this as CCNotesGrouped.LatestDate need to be a unique field as it is a primary key? So I changed the relationship from 1:1 to a 1:M (CCNoteDate.CalendarDate : CCNotesGrouped.LatestDate). This allowed the change to Date for both fields:

 

Relationship.jpg

 

However, I am still getting the below bar chart:

 

Current bar chart2.jpg

 

Do you have any further guidance on what I can do? Cheers

ansa_naz
Continued Contributor
Continued Contributor

I created a duplicate of the LatestDate field and changed it to Date type in Edit Query - this has fixed the issue

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.