Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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:
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:
If anyone can help that would be marvellous!
Cheers
Solved! Go to Solution.
I created a duplicate of the LatestDate field and changed it to Date type in Edit Query - this has fixed the issue
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.
Check the data type, by the looks of your fields pane it's not a date?
Hi @jthomson its definitely a date, see screenshots:
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:
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.
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.
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:
However, I am still getting the below bar chart:
Do you have any further guidance on what I can do? Cheers
I created a duplicate of the LatestDate field and changed it to Date type in Edit Query - this has fixed the issue
User | Count |
---|---|
127 | |
108 | |
99 | |
63 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |