DAX Left Anti Inactive Relationship

[smpa01]

I have two tables called 'left' and 'right'

| left.colA | left.Val |
|-----------|----------|
| 1         | A        |
| 2         | B        |
| 4         | D        |
| 5         | E        |


| right.colA | right.colB |
|------------|------------|
| 1          | 3          |
| 2          | 4          |
| 3          | 5          |

They have one ACTIVE (left.colA-right.colA) and one INACTIVE (left.colA-right.colB) relationship.  

I want to perform a DAX query that generates what a LEFT ANTI join (FIlter 'left' such as it does not contain any value in left.colA that exists in right.colB) would produce on tbl[left] where the join columns are left.colA to right.colB.

So I want to end up with the following 

Now, if I perform a join on ACTIVE relationship columns, I can take advantage of in-built ISEMPTY. But is there any way to utilize ISEMPTY if the joins are on INACTIVE relationship columns? I want to avoid COUNTROWS as I suspect it will be a lot slower on a large dataset [Ref-SQLBI]

Table = 
var leftAntiActiveRelation = FILTER('left',NOT ISEMPTY(RELATEDTABLE('right')))
var rightAntiInActiveRelation = FILTER('left',CALCULATE(COUNTROWS('right'),USERELATIONSHIP('left'[left.colA],'right'[right.colB]))=0)
return rightAntiInActiveRelation

@AlexisOlson @CNENFRNL @OwenAuger 

Thank you in advance.

Sample pbix is attached

[AlexisOlson]

You can also skip relationships entirely and use right.colA or right.colB the same way like this:

Table =
VAR _Right = VALUES ( 'right'[right.colB] )
RETURN
    FILTER ( 'left', NOT 'left'[left.colA] IN _Right )

I'm not sure about the efficiency of this though. This might be better since it doesn't have an iterator:

Table =
VAR _ColA =
    EXCEPT ( VALUES ( 'left'[left.colA] ), VALUES ( 'right'[right.colB] ) )
RETURN
    CALCULATETABLE ( 'left', TREATAS ( _ColA, 'left'[left.colA] ) )

[CNENFRNL · Accepted solution]

Hi, @smpa01 , you always come up with tricky and interesting questiongs.

In theory, the expected result can be achieved this way,

But from perspective of modeling, such an anti-join is a typical violation of reference integrity