DirectQuery Cross-filtering compromised when using "DateTime" in table visual

[Anonymous]

Explaining using an example. This is for DirectQuery. Issue doesn't persist when using Import. The issue is how DirectQuery handles DateTime, but this does not apply to Date.

Table 1. Both in model + visual. On the "One" side of One-to-Many:

Column 1(PK)= ID

Column 2= Sum

Column 3= DateTime

Table 2. Both in model + visual. On the "Many" side of One-to-Many:

Column 1(FK)= ID

Column 2=info from table

Filtering on Table 1 does not correctly crossfilter on Table 2. However, when removing DateTime(Column 3) the crossfiltering works. There should be no reason for this to happen.

My solution to this was....interesting. By removing DateTime and replacing it with a Text Calculated Column that appeared to be DateTime, the visual worked.

Replaced DateTime(column 4) with the following:

Sketchy Workaround Calculated Column =
RIGHT("0" & MONTH(Table1[DateTime Column]),2) & "/" & RIGHT("0" & DAY(Table1[DateTime Column]),2) & "/" & YEAR(Table1[DateTime Column]) & " " & RIGHT("0" & HOUR(Table1[DateTime Column]),2) & ":" & RIGHT("0" & MINUTE(Table1[DateTime Column]),2) & ":" &RIGHT("0" & SECOND(Table1[DateTime Column]),2)

[v-qiuyu-msft]

Hi @Anonymous,

Would you please share SQL DDL and DML with us so we can try to create sample tables in SQL database? 

May I know which Power BI desktop version you are running? 

Best Regards,
Qiuyun Yu