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.
In a nutshell
Use a LEFT JOIN and a calculated column.
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.
- 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);
- 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.