DirectQuery SQL Convert datetime to date

Another question, are you connecting to a table/view or are you using a stored procedure/sql command directly in Power BI? This can only be done if you are connecting to an actual structure and not using a sp or sql command within Power BI. 

The correct fix for this situation is to create a calculated column with DATEVALUE() formula in DAX. Spent hours trying to troubleshoot but this one finally worked. This is the only solution that worked for me in DirectQuery mode.

No other datetime to date conversions in PBI Desktop were effective since the sql query getting generated kept ignoring this data type conversion. However, using DATEVALUE(), it effectively creates the correct SQL query when fetching data and even works if you have joint this calculated column to columns from other DirectQuery tables.

See how it creates the SQL:
SELECT CAST(CAST(CAST([t3].[modifiedon] AS VARCHAR(4000))AS DATE) AS DATETIME) AS [_modifiedon1 (DateOnly)]
FROM table1 AS [t3]