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.