Create relationship between two columns without unique values?

[Anonymous]

I'm trying to relate two date&time columns in two separate tables.

One column captures the date&time a prospect entered my recruitment system.

The other column captures the date&time of unique page views from our websites google analytics.

Because of the volume of data in each of these tables, there are a few duplicate values in each column. This is not a flaw in the data. Is it impossible to create relationships between columns that do not have 100% unique values? What are my options, how should I proceed with this? 

[jcarville · Accepted solution]

@Anonymous, it is always good practice to have a separate date table, something like the following will help you create one: https://www.agilebi.com.au/blog/power-bi-date-dimension

I would then join each of your 2 tables to the separate date table to get around your issue.

[Anonymous]

@jcarville Awesome idea! That worked exactly as I needed it to. Really appreciate it. 

[Anonymous]

In the past what i've done to overcome this challenge is simply hop in the query editor and "remove duplicates" from the data set. I wouldnt advise you do this is many duplicates exist but if its a matter of very minimal data points causing the issue it can at least get you started. 

Many to many relationships cannot exist in PBI as far as I know.

[Anonymous]

@Anonymous Considering the scope of my inquiry I can probably get away with this, at least as a stopgap solution! Thanks.