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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
olivierlaloo
Regular Visitor

Relationship with date table not working

Hi All, I'm fairly new to Power BI and got stuck on something that seems so easy in tutorials, but on my project, haven been spending hours finding the reason why... So, i have created a new PBI based on Data in Dynamics 365 (CRM). I have retrieved tables 'Accounts', 'Salesorders' & 'SalesOrderDetails'. My SalesOrder table contains a field 'RequestDeliveryBy' which i want to use for time intelligence. I have created a new calendar table (as shown in the different tutorials) and have linked my calendar table with the field 'RequestDeliveryBy'. Now, when i try to make a simple repport showing the amount or orders per year, this is not working... Anybody has any idea?

 

Date calendarDate calendarRelationshipRelationshipOutcome in repotOutcome in repotRequestDeliveryByRequestDeliveryBy

 

Note: I have tried a workaround, where i have created a new field in Salesorders & Date where i have formatted the Dates fields as 'YYYYMMDD'' and made the link on both tables to this new formatted field and then it works. However, when this link is created like this, i'm not able to use formulas such as TOTALYTD, TOTALMTD, since this new formatted field is not of type date. 

Let me know your thoughts!

1 ACCEPTED SOLUTION
Seth_C_Bauer
Community Champion
Community Champion

@olivierlaloo Check to see whether or not the data type is date in the "Edit Query" section. I've run into this before and wasted a bunch of time because I didn't check there. My guess, is that it is still text in that section. (Load vs. Model, they both need to have the same datatypes for some reason).


Looking for more Power BI tips, tricks & tools? Check out PowerBI.tips the site I co-own with Mike Carlo. Also, if you are near SE WI? Join our PUG Milwaukee Brew City PUG

View solution in original post

22 REPLIES 22
c60918
New Member

I read through this post thinking it would solve my issue, as I thought I had an issue with the Power Query Editor date format and cannot for the life of me find the problem.  Every time I try to merge or create a relationship with the Calendar (dimension) table to any other fact table (in this case Hire YTD) it comes back with no matches.  The dates in the Calendar (dimension) table go from 7/16/11 to 2/15/29, so my fact table with 2022 dates shouldn't have a problem finding a match.  I don't understand what is going on here.  Thanks in advance.  If you respond, @Seth_C_Bauer, I will be ecstatic! I love your podcast with Mike and Tommy, excellent stuff, wish I could be at your level already:)Hire YTD - Date FormatHire YTD - Date FormatCalendar - Date FormatCalendar - Date FormatJoin - Calendar to Hire YTDJoin - Calendar to Hire YTD Merge - no Hire YTD dataMerge - no Hire YTD data

Anonymous
Not applicable

Hello, as some people I am quite new in the use of Power Bi (since March 2018) and got stucked with a relationship between two data tables.

I am pulling information from ms excel and from my point of view have a linear relationship, however I am not able to get the information at detail, once I try to get the data I get the total result in each line instead the detail sum of that particular data.

I hope you guys can help me, I solve this by feeding the main table with lots of vlookups and now I am able to retrieve the information as I wanted, however I thought relationships are equivalent to vlookups.

 

Thank you

 

Anonymous
Not applicable

Hello, as some people I am quite new in the use of Power Bi (since March 2018) and got stucked with a relationship between two data tables.

I am pulling information from ms excel and from my point of view have a linear relationship, however I am not able to get the information at detail, once I try to get the data I get the total result in each line instead the detail sum of that particular data.

I hope you guys can help me, I solve this by feeding the main table with lots of vlookups and now I am able to retrieve the information as I wanted, however I thought relationships are equivalent to vlookups.

 

Thank you

AlanBaker
Frequent Visitor

Hi

I have what appears to be the same issue, I have Created a Calendar Table with a Unique ID which I am using to perform Date Intelligence in order to determine planned costs for the previous week and comparing the result to the current week. You will see two tables below the first selects the fields I require BUT uses the Calendar Table "Date" field this appears to be working AOK.

 

The SECOND Table mirrors the first table EXCEPT that I have removed the Calendar "Date" field and substituted the "Week Begining" field from my data table and as you can see the dates between the two tables do NOT appear to be linking as the reults for the previous week are NOT populated?

CalendarError1.JPG

 

I have read the helpful advice in the replies to this original lost below and I believe that the imported table "Week Begining" field is formatted as Date d/mm/yyyy within the PBI Query Editor. 

 

  Query EditorQuery EditorThe Data View of the Calendar table creationThe Data View of the Calendar table creation

 

The Calendar table which is created within the PBI Data View is formatted again as Date. I have Applied the Queries and Refreshed the graphics and data tables (several times 🙂

 

The Calendar Table is Linked as a ONE from the "Date" Column to the MANY column 'WeekBegining" field in the data table.

 

Help to solve this would be greatly appreciated....

Seth_C_Bauer
Community Champion
Community Champion

@olivierlaloo Check to see whether or not the data type is date in the "Edit Query" section. I've run into this before and wasted a bunch of time because I didn't check there. My guess, is that it is still text in that section. (Load vs. Model, they both need to have the same datatypes for some reason).


Looking for more Power BI tips, tricks & tools? Check out PowerBI.tips the site I co-own with Mike Carlo. Also, if you are near SE WI? Join our PUG Milwaukee Brew City PUG

This is the right answer! Thanks!

This is not the issue as both are selected as dates in query editor.  My table only seems to correlate to one specific date and ignores all the others.

I attempted to change the data type in the query from the Transform Data window, but PowerBI still fails to correlate correctly with the calendar table. Do you have any other ideas on how to fix this? Is this something where the file or query is corrupted for some reason and the process has to be started over? Thanks.

FFSk, many and MANY hours spent trying to understand what was the problem as all the data looked right, and BI was just "masking" the issue... I can't believe it!!! Thanks!

Thank you for this input. This has been really helpful. 

My data type is showing as date but whenever itry to right my dax, my start date doesnt come up

 

I'm so glad I googled it after only 10 minutes of banging my head against the wall.
THANK YOU for saving me hours, cheers

Woooow !!!! I've been trying to solve this problem for days, I've looked at all the videos I found, on all the sites, I've done a thousand tests and nothing... And finally here is the solution. You are extremely supportive!!!! 

This has just saved me from pulling my hair out after hours of wondering why some dates were showing as 'blank', but had dates in the fields! 

Add another appreciation for this tip!

Anonymous
Not applicable

God bless you, you saved my day 😄 😄

I almost wasted 2 hours figuring out why the date join is not working.. it was the datatype difference in Edit Query section..Thanks!

Yes!

Finally it worked, why.... oh why.... is it like this....

Thanks!! 🙂

Anonymous
Not applicable

@Seth_C_Bauer This worked like a charm. The data type needs to be set as "Date" in the Query Editor, just changing in the loaded data model does not work. Thanks!

Anonymous
Not applicable

Hi @Seth_C_Bauer, I am facing same problem. I have created date table using DAX in Power BI desktop. As I don't have date table in my source database. I have checked other dates types like order date and invoice date they match in data type both in Power query and report window. Can you please advice me what else can be problem???

 

 

Thanks in advance!!

@Anonymous Can you give an example of what the issue is specifically?


Looking for more Power BI tips, tricks & tools? Check out PowerBI.tips the site I co-own with Mike Carlo. Also, if you are near SE WI? Join our PUG Milwaukee Brew City PUG

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.