cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
jk125 Frequent Visitor
Frequent Visitor

Anti Join

Hi there,

 

I am wondering if there is a way to Anti join as DAX column/measure?  I have the following relationship:

 

Capture.JPGRelationship

 

 

 

 

 

 

 

The Relationship has to be like this.

What I will do is create two slicers, both with dates.   The Table A date slicer will be a single select, and I will allow the user to choose any date (multiple) from Table B date slicer.  I will then create two tables that show the values, from both Table A and Table B.  The idea here is to see at Date A, which ID's have occured into Date B, which would be reflected in the Table B table (Inner Join with Table A).  I would like to show a third table, that show's values that are in Table A but not in Table B (Anti Join) as DAX column/measure while slicing both date slicers.

 

After selecting the dates and the inner join occurs:

Table A has the values (A,B,C,D,E)

Table B has the values (B,C)

 

I want a third table that does the anti join

Table C has the values (A,D,E)

 

Thanks!

1 ACCEPTED SOLUTION

Accepted Solutions

Re: Anti Join

Hi @jk125 ,

 

By my test , you can create a calculated  table using EXCEPT function to implement left anti join and use the following formula to implement inner join. In  my sample, the table1 is your table A.  the  table inner join is your table B.  And the table left anti join is your table C.

 

  1. Create the calculated table “left anti join” and “inner join”

 

left anti join = EXCEPT(Table1,Table2)

0.png

inner join =

VAR a =

    ADDCOLUMNS (

        CALCULATETABLE (

            Table1,

            FILTER ( Table1, 'Table1'[ Date] IN VALUES ( 'Table2'[ Date] )  )

        ),

        "datea", [ Date]

    )

VAR b =

    ADDCOLUMNS (

        a,

        "table2.spent", LOOKUPVALUE ( Table2[spent], Table2[ Date], [datea] ),

        "table2.id", LOOKUPVALUE ( Table2[id], Table2[ Date], [datea] )

    )

RETURN

    b

1.png

 

  1. Manage the relationships with table “inner join” and “left anti join”. Choose Cardinality to  many to one.

2.jpg3.jpg

 

  1. Then get the results.

4.jpg5.jpg

 

Best Regards,

Xue Ding

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

1 REPLY 1

Re: Anti Join

Hi @jk125 ,

 

By my test , you can create a calculated  table using EXCEPT function to implement left anti join and use the following formula to implement inner join. In  my sample, the table1 is your table A.  the  table inner join is your table B.  And the table left anti join is your table C.

 

  1. Create the calculated table “left anti join” and “inner join”

 

left anti join = EXCEPT(Table1,Table2)

0.png

inner join =

VAR a =

    ADDCOLUMNS (

        CALCULATETABLE (

            Table1,

            FILTER ( Table1, 'Table1'[ Date] IN VALUES ( 'Table2'[ Date] )  )

        ),

        "datea", [ Date]

    )

VAR b =

    ADDCOLUMNS (

        a,

        "table2.spent", LOOKUPVALUE ( Table2[spent], Table2[ Date], [datea] ),

        "table2.id", LOOKUPVALUE ( Table2[id], Table2[ Date], [datea] )

    )

RETURN

    b

1.png

 

  1. Manage the relationships with table “inner join” and “left anti join”. Choose Cardinality to  many to one.

2.jpg3.jpg

 

  1. Then get the results.

4.jpg5.jpg

 

Best Regards,

Xue Ding

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.