Same query in imported vs direct query wont connect to Date Table

[Rosenmeyer]

I have 2 identical queries that are respectively imported query and one direct query.

I need to make the imported query in direct query so I can make a scheduled refresh.

I have a date in the imported sales table [ClosedAt] that is Date/Time, I try and extract the date in a custom column [ClosedAtDate]=

ClosedAtDay = DATE(YEAR('Sales import'[ClosedAt]);MONTH('Sales import'[ClosedAt]);DAY('Sales import'[ClosedAt]))

I make a connection in the relationship model and it works with filters.

I do the same for the direct query table and this error happens when I try to filter anything by slicer:

[v-shex-msft]

Hi @Rosenmeyer ,

According to your error message, it seems like power bi not able to recognized dynamic table field records. I'd like to suggest you use measure to calculate on row contents level.

Regards,

Xiaoxin Sheng

[Rosenmeyer]

@v-shex-msft 

Hi, if I understand you correctly, when I create a measure with the same formula this happens:

[v-shex-msft · Accepted solution]

HI @Rosenmeyer ,

You can't direct use column in measure formula, please add aggregation function to summary column value:

ClosedAtDay =
VAR currDate =
    MAX ( 'Sales import'[ClosedAt] )
RETURN
    DATE ( YEAR ( currDate ); MONTH ( currDate ); DAY ( currDate ) )

Calculated Columns and Measures in DAX 
Regards,
Xiaoxin Sheng