0 matches when merging two date columns between two tables

[Anonymous]

I am Trying to pull Relative day and week by merging two tables using Date column but for some reason it is returning 0 matches. Both date columns are in both tables have same format (Date).

I’m confused as to why it is not working.

Any help is greatly appreciated.

Thanks,

Deevs

[V-pazhen-msft]

@Anonymous 

First make sure the data type of the referenced columns are the same. Remove the non-printable characters in these two columns and try to merge again.

Paul Zheng _ Community Support Team
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous · Accepted solution]

I have done all possible things I could do. Please read through my previous comments.

• Changed the date format to same format on both tables – didn’t work
• Cleaned and Trimmed date columns to remove the unnecessary spaces and characters  - didn’t work either

I want to reiterate my last comments. it’s the multi relationship between Calendar date and other tables causing the issue for some reason.

I just duplicated the calendar table and merged it with F_ETG_Daily and it worked whereas merging with original Calendar tables doesn’t work

[edhans]

@Anonymous You should really consider get rid of those bi-directional relationships. Those are not to be used casually. Microsoft recommends minimizing use of both Many-to-Many and Bi-Directional Relationships. In other words, unless you are a DAX expert, find another way to remodel your data to conform to a Star Schema and don't use these two features. I avoid them both at all costs.
Microsoft Guidance on Many-To-Many Relationships
Microsoft Guidance on Bi-Directional Relationships
Microsoft Guidance on Importance of Star Schema

Also note that the reason many here couldn't assist properly is the terms used. Those are not merges. Merging can only be done in Power Query. Those are filter relationships, which are very different, and it was allowing the relationship, it just was not allowing you to activate it, because it causes ambiguity in the model. That is 100% ok if you are really good at DAX. Like, an 8 or above in a scale of 1-10 and know what you are doing and need bidirectional for a specific reason. But if you are like me, you should leave those off, as per MS guidance and the articles/videos linked to above.

[Anonymous]

below is the workaround :

[amitchandak]

@Anonymous , I doubt your date on F_ETG_Daily has a timestamp. That is not visible because of the format . Change the data type to datetime and format having the time you can see that.

Create a date column like this

New Date =[Date].Date and join it with your calendar table. Make sure the calendar table has all the dates required.

[Anonymous]

@amitchandak 

I have tried every possible option including what you have suggested. Please see below still same issue.

Can’t this be a bug in power BI ?

[Anonymous]

any one knows about a solution for this issue ?

[Anonymous]

Adding to that F_ETG_Daily data coming from SQL Server and  D_Calendar Data from Sap Sybase IQ  is that something preventing it from matching date column values between tables.

[edhans]

@Anonymous both date formats have to be the same. Make sure they are both a true date. Can you confirm that both columns are a date format and look like this in Power Query:

You do not want a DateTime field as that wastes space in Power BI when the data loads, and you'll almost never get a match at something down to the seconds level. Date only.

If this doesn't answer the question, please post a screenshot of both fields in these tables.

[Anonymous]

@edhans @amitchandak 

I can confirm that Date column on both tables are on the same format (I .e )

I found something interesting.

D_Calendar is already connected to a Fact table call D_EGM_Daily   and D_Begin_Effctive_dates.

Then I have created a separate Calendar table called and D_Cal for ETG Data and merged D_EGM_Daily    with D_Cal for ETG Data and it worked .

Hence it implies that data cannot be merged between two tables using a date column if the table that sits date as primary key connected to multiple tables.

The ideal case would be to use the main Calendar table to merger rather than creating a duplicate of the main Calendar. Let me know your thoughts.

Thanks,

Deevs

[edhans]

There is nothing I am aware of in Power Query that cares about joins once the data model is loaded. Are the dates of the same locale? If one date column is MM/DD/YYYY and the other is DD/MM/YYYY they may not merge. I've not tested that theory out, but I cannot tell base on your first post and most recent post that you have a consistent format.

Maybe post some of the data here so we can test with it?

As to all of those bi-directional relationships. Do you really need all of those? Microsoft recommends minimizing use of both Many-to-Many and Bi-Directional Relationships. In other words, unless you are a DAX expert, find another way to remodel your data to conform to a Star Schema and don't use these two features. I avoid them both at all costs.
Microsoft Guidance on Many-To-Many Relationships
Microsoft Guidance on Bi-Directional Relationships
Microsoft Guidance on Importance of Star Schema