Can't use left anti join with DirectQuery

[ReadTheIron]

I have a very large table ("All Failures") I'm connected to with DirectQuery. I'm trying to filter these records so as to exclude the ones that appear on a much smaller list ("Disqualified Failures"). I tried Merge Queries > Left Anti Join and get "The selection excludes 35 of 42841 rows from the first table", which is exactly what I'd hope for. But then I get "This step results in a query that is not supported in DirectQuery mode". Is there a way to filter "All Failures" without switching it to Import mode?

[sjoerdvn]

If you really want to use DirectQuery, you might want to create a view in your source (Spark SQL I guess?) and do the anti join there. Also, If you're using DirectQuery you might want to limit the number of visuals on a single report page...

[Anonymous]

Hi @ReadTheIron ,

According to your statement, I think you want to to filter your table when you load it from your databased by Direct Query connection mode.

As far as I know, there are some limitation on data transformation in Direct Query.

For reference: DirectQuery in Power BI - Power BI | Microsoft Learn

I suggest you to add SQL statement in advance options.

Or you may load tables and then create relatioship between the fact table and the small table.

Then create the visual by key columns in small table.

Best Regards,
Rico Zhou

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

[ReadTheIron]

I'm afraid I don't know what to do with SQL statement in advance options, and it's not just one visual I want to create with the filtered table, it's all the visuals in my report. Thanks for answering though!

[rajendraongole1]

Hi @ReadTheIron -, you'll need to approach the problem using a method that avoids complex transformations like merges, which are unsupported in DirectQuery. 

create a measure as below

ExcludedFailures =
IF(
COUNTROWS(
FILTER('Disqualified Failures',
'Disqualified Failures'[FailureID] = MAX('All Failures'[FailureID])
)
) > 0,
BLANK(),
1
)

Hope this helps.

[ReadTheIron]

I was afraid this would be the answer, since I am basing the entire report on "All Failures". The measure works to create it, but I'm not sure how to use it as a filter in the variety of visuals I'm creating based on the "All Failures" table. Thank you for responding though!

[Anonymous]

Hi @ReadTheIron ,

Could you tell me if your issue has been solved? If it is, kindly Accept the helpful reply as the solution. More people will benefit from it. Or you are still confused about it, please provide me with more details about your issue.

Best Regards,

Rico Zhou

[ReadTheIron]

None of the proposed solutions helped; I ended up using Import instead of DirectQuery with a filtered table.