Anti Join

[Anonymous]

Hi there,

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

Relationship

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!

[v-xuding-msft · Accepted solution]

Hi @Anonymous ,

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)

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. Manage the relationships with table “inner join” and “left anti join”. Choose Cardinality to  many to one.

1. Then get the results.

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.