Relationship between Date and DateTime column in Power BI

In Power BI if you create a relationship between a Date and DateTime column then you will see that the relationship doesn't work or if it works then it only works for time that is 12:00AM / Midnight.

You can overcome this limitation by changing the Join On Date Behavior property in Tabular Editor.

First I have created a DateTime version of OrderDate in Sales table

createOrReplace

	ref table Sales

		column OrderDateTime = Sales[OrderDate] + ROUND ( RAND(), 2 )
			formatString: General Date
			summarizeBy: none

			annotation SummarizationSetBy = Automatic

Then a relationship between Sales and Dates table:

createOrReplace

	relationship 47afef3b-726d-e846-9485-f167ed97ede0
		isActive: false
		fromColumn: Sales.OrderDateTime
		toColumn: Dates.Date

The measure that uses this relationship:

Sales Amount DateTime = 
CALCULATE ( 
    [Sales Amount],
    USERELATIONSHIP ( Sales[OrderDateTime], Dates[Date] )
)

As you can see only when the Sales[OrderDateTime] is "SomeDate" + 12:00 AM it shows the Sales Amount otherwise it returns a blank and due to this Referential Integrity Violation engine also added the invisible blank row in the Dates table as can been seen in the slicer.

There are several ways to fix this:

1. Split Sales[OrderDateTime] into Sales[OrderDate] and Sales[OrderTime]

2. Truncate the column and remove the Time part

3. or use Tabular Editor to change the property to join only on Date part

Once you select DatePartOnly and save changes and Refresh the model the measure will start working.