OLE DB or ODBC error: Specified column in the 'SUMMARIZE' function as not found in the input table

[AlvinLy]

Hello,

I have an odd situation here.

So I have a direct query fact table (connected to our data warehouse) and several dimension tables in my Power BI setup. This is the error I get when I put this measure into my visual

Here are some more information on this measure. The code of this measure is as follows:

Sch Start Period Measure = 
VAR CP = SELECTEDVALUE(Fact_Table[Control Point Number])
VAR DelID = SELECTEDVALUE(Fact_Table[Deliverable ID])
VAR enddate = SELECTEDVALUE(Fact_Table[Schedule Date])
VAR startdate = 
IF( CP > 1,
    CALCULATE(
        MIN(Fact_Table[Schedule Date]),
        Filter(ALL(Fact_Table), Fact_Table[Deliverable ID] = DelID),
        Filter(All(Fact_Table), Fact_Table[Control Point Number] = CP-1)
    ),
    enddate
)
RETURN
CALCULATE(
    Min(Date_Table[Period End Date]),
    Filter(Date_Table, Date_Table[Period End Date] > startdate)
)

//startdate

If you notice i have //startdate at the bottom. I was testing if anywhere else in my code there were issues and there wasn't (ie the startdate formula in the measure works as intended). The only part that seems to be giving me an error is the calculate after the RETURN.  

Note that the table names were altered and the error was partially redacted for privacy concerns

Any ideas on what might be causing this?

[Anonymous · Accepted solution]

Hi @AlvinLy 

Your DAX measure is quite complex and combines data from different contexts. In DirectQuery mode, not all filtering actions behave as they would in an in-memory model (Import mode). Here are some points to consider:

Complex Filters: The filters and calculations you're applying might be too complex or might result in a query that SQL Server (or whatever your backend is) cannot process efficiently or at all. In DirectQuery mode, each DAX expression translates into a SQL query executed on the database, and some SQL servers have limitations on subqueries or complex joins.

Filter() Function Over DirectQuery: Using 'Filter(ALL(...))' in DirectQuery mode can sometimes lead to performance issues or limitations because it requires scanning the entire table. The combination of these filters might be generating a query that is not supported or is too complex.

Best Regards,

Jayleny

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.