Forum Discussion
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 calendarRelationshipOutcome in repotRequestDeliveryBy
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!
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).
23 Replies
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).
Hi Anonymous, you were right. The issue was that in the Edit Query section, the field was formatted as date + time + timezone instead of date+time.
Changing this field + apply + reload all data solved the trick.
Thanks!
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!!!!
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 FormatCalendar - Date FormatJoin - Calendar to Hire YTD Merge - no Hire YTD data
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?
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 EditorThe 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....
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
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
I know I'm late to this post, but mine started working when changing the type in power query from date/time to date.
