cancel
Showing results for 
Search instead for 
Did you mean: 
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

@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

10 REPLIES 10
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....

@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

Yes!

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

Thanks!! 🙂

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

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!!

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

Hi @Seth_C_Bauer, Sorry for late replay

I am designing a weekly sales report for last four week. By  stores and  categories. but when I am adding week number column to X-axis , I am getting blank in the report visualization.before adding week column to X-Axisbefore adding week column to X-Axisafter adding week number column to X-axisafter adding week number column to X-axisimage.png

Hi @Seth_C_Bauer, 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!

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Get Ready for Power BI Dev Camp

Power BI Dev Camp - June 24th

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors