Forum Discussion

smpa01's avatar
smpa01
Community Champion
3 years ago
Solved

DAX Left Anti Inactive Relationship

I have two tables called 'left' and 'right'   | left.colA | left.Val | |-----------|----------| | 1 | A | | 2 | B | | 4 | D | | 5 | E ...
Need Help
  • CNENFRNL's avatar
    CNENFRNL
    3 years ago

    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

     

AlexisOlson's avatar
AlexisOlson
Super User
3 years ago

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] ) )