PowerBI allows Left Anti joins (

However, this function is not compatible with the "direct query" mode.

Here is a way to perform Left Anti joins using functions compatible with the "direct query" mode.


In a nutshell

Use a LEFT JOIN and a calculated column.

In detail

In SQL, you want to achieve the following SQL : SELECT * FROM Table1 t1 LEFT JOIN Table2 t2 ON = WHERE IS NULL.


In indexima:

  • For table2, create a calculated field that is the copy of the key used in the join, like this:

ALTER TABLE table2 ADD COLUMNS (id_copy as id);

In PowerBI:

  • Create a new column with the following formula: ISBLANK([id_copy]).
  • In the model, define a standard LEFT join between the 2 tables.
  • Use the new column as a filter on the report level and apply filters on the "empty" value.