cancel
Showing results for 
Search instead for 
Did you mean: 

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

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)

Status: New
Comments
Moderator

Hi @bhanson41,

 

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