I recieved the same error "A function 'MAXX' has been used in a True?False expression that is used as a table filter expression. This is not allowed."
Also, it might help to know that I am using Direct Query Mode.
I modify the formula to move this condition into filter function, maybe you can try it.
Scheduled Starts Yesterday = VAR filtered = FILTER ( ALL ( Table1 ), Table1[SECT_NO] = "1234" && Table1[SLS_MDL] <> "18C" && Table1[SLS_MDL] <> "12C" ) VAR nearest_date = MAXX ( FILTER ( filtered, [SCH_STRT_DT] < TODAY () ), [SCH_STRT_DT] ) RETURN CALCULATE ( COUNTROWS ( Table1 ), FILTER ( filtered, Table1[SCH_STRT_DT] = nearest_date ), PKG_ORD[SLS_MDL] <> "9C" )
I'm not so sure if this formula is fully supported in directquery mode.
You may be right but there has to be another way.
Right now I am getting the error "Function 'FILTER' is not supported in this context in DirectQuery mode".
What would that context be? I use the 'FILTER' function a lot in DireectQuery mode.
You can turn on the 'Allow unrestricted measures in DIrectQuery mode' option to use unsupported dax function.
>>I feel like we're getting closer, now the measure returns no data "(Blank)".
If you can, please share a pbix file for further test.
Please check below link to know the supported datasources.
BTW, below are limitation about directquery, I think you can take a look on it.
There are currently a few limitations to using DirectQuery:
All tables must come from a single database
If the Query Editor query is overly complex, an error will occur. To remedy the error you must either delete the problematic step in Query Editor, or Import the data instead of using DirectQuery. For multi-dimensional sources like SAP Business Warehouse, there is no Query Editor
Relationship filtering is limited to a single direction, rather than both directions (though it is possible to enable cross filtering in both directions for DirectQuery as a Preview feature). For multi-dimensional sources like SAP Business Warehouse, there are no relationships defined in the model
Time intelligence capabilities are not available in DirectQuery. For example, special treatment of date columns (year, quarter, month, day, so on) are not supported in DirectQuery mode.
By default, limitations are placed on DAX expressions allowed in measures; see the following paragraph (after this bulleted list) for more information
There is a 1 million row limit for returning data when using DirectQuery. This does not affect aggregations or calculations used to create the dataset returned using DirectQuery, only the rows returned. For example, you can aggregate 10 million rows with your query that runs on the data source, and accurately return the results of that aggregation to Power BI using DirectQuery as long as the data returned to Power BI is less than 1 million rows. If more than 1 million rows would be returned from DirectQuery, Power BI returns an error.
There has to be another way.
I created a measure
Previous Date = CALCULATE(MAX(Table1[SCH_STRT_DT]),FILTER(Table1,Table1[SCH_STRT_DT]<TODAY())
and tried to use this as a filter input. I am now getting a value, just the wrong value.
Starts = CALCULATE(COUNTROWS(Table1),FILTER(ALL(Table1),Table1[SCH_STRT_DT]=[Previous Date] && Table1[SECT_NO]="04114" && Table1[SLS_MDL]<>"18C" && Table1SLS_MDL]<>"9C" && Table1[SLS_MDL]<>"12C"))
I'd like to suggest you use a variable to store the pervous date.
Starts = var Previous_Date = CALCULATE(MAX(Table1[SCH_STRT_DT]),FILTER(Table1,Table1[SCH_STRT_DT]<TODAY()) return CALCULATE(COUNTROWS(Table1),FILTER(ALL(Table1),Table1[SCH_STRT_DT]=Previous_Date && Table1[SECT_NO]="04114" && Table1[SLS_MDL]<>"18C" && Table1SLS_MDL]<>"9C" && Table1[SLS_MDL]<>"12C"))
Notice: if you drag a measure into a filter function as a parameter, sometimes it may get the wrong result.