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        |


| 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

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

     

2 Replies

  • CNENFRNL's avatar
    CNENFRNL
    Community Champion
    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] ) )