Forum Discussion

Rosenmeyer's avatar
Rosenmeyer
Frequent Visitor
6 years ago

Custom Date column won't connect to Date Table

I have 2 identical SQL queries that are respectively imported query and one direct query from an Azure SQL database.

 

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 extract the date in a custom column [ClosedAtDate], because my Date/Time column [ClosedAt] won't connect unless it's only the date=

ClosedAtDay = DATE(YEAR('Sales import'[ClosedAt]);MONTH('Sales import'[ClosedAt]);DAY('Sales import'[ClosedAt]))
I make a connection to the date table with !imported SQL query! 'imported Sales'[ClosedAtDay] to 'DateTable'[Date] in the relationship model and it works with date filters and slicers.
 
I do the exact same for the !direct query table! and this error happens when I try to date filter anything by date slicer:
 
Both queries are identical.
Need Help

5 Replies

    • Rosenmeyer's avatar
      Rosenmeyer
      Frequent Visitor
      6 years ago

      parry2k If I change the column to Date it just makes everything go (Blank) when I try to filter by date slicers.

      • v-lid-msft's avatar
        v-lid-msft
        Icon for Community Support rankCommunity Support
        6 years ago

        Hi Rosenmeyer ,

         

        I do the exact same for the !direct query table! 

        Could you please share the formula that you create the column "CloseAtDate" for Directquery table? Are you creating it in the Power Query Editor?


        Best regards,