cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jk125
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:

 

RelationshipRelationship

 

 

 

 

 

 

 

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
v-xuding-msft
Community Support
Community Support

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.

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.

View solution in original post

1 REPLY 1
v-xuding-msft
Community Support
Community Support

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.

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.

View solution in original post

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors