CrossJoin happens on Date column relation in July 2017 update

[Rajiv1237]

I have joined two tables on date column. Instead of inner join Power BI doing cross join.

Column Names are CreatedOnDate and PK_Date.

[Rajiv1237 · Accepted solution]

Hello All,

I got the solution. It is date format issue (Weird)

My one column Date format was dd/mm/yyyy and other table column format was mm/dd/yyyy.

I changed that format in my SQL table and repopulated the data and now relationship is working.

Seriously I never thought Date format casued this issue. Very Weird.

[bullius]

Have you tried double-clicking on the relationship and changing the "Cross filter direction"?

[Rajiv1237]

Yes tried that

[Rajiv1237]

Hello All,

I have pulled data from OData Feed into Power BI and joined two tables based on dates and that join is not working.

I converted the datatype of both column to Date.

Moment I select columns from both tables data goes off. Quite weired. Both column is of Date datatype.

[Rajiv1237]

To fix this issue, I have created new column in Power BI desktop using formula 

CreatedOnDate = DATE(YEAR(sab_offerdetailses[CreatedOn]), MONTH(sab_offerdetailses[CreatedOn]), DAY(sab_offerdetailses[CreatedOn])).

After that I made a relationship between tables on CreatedOnDate and PK_Date column. Instead of inner join it does cross join.

Now I am scratching my head 😞

[Rajiv1237]

Raw data of joining columns