Left.Anti

Context

PowerBI allows Left Anti joins (https://docs.microsoft.com/en-us/power-query/merge-queries-left-anti)

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.

Resolution

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 t1.id = t2.id WHERE t2.id IS NULL.



Actions

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.