Join 2 tables with many to many relationship and compare

[Anonymous]

Hi, 

We have two tables A and B.  We have two requirements to achieve our report. 

A Table : 

Code     Production Date

100001 6/10/2020

100001 6/15/2020

100002 6/16/2020

100001 6/17/2020

100003 6/18/2020

100004 6/19/2020

B Table

Code   Sub Code

100001 030401

100001 005020

100002 302003

100002 422322

100002 112321

100003 543433

1. To join this two tables , for example, -- It's many to many relation. Not sure how to do that in DAX.

Code   Sub Code   Date

100001 030401 6/10/2020

100001 005020 6/10/2020

100001 030401 6/15/2020

100001 005020 6/15/2020

100002 302003 6/16/2020

100002 422322 6/16/2020

100002 112321 6/16/2020

..... ..... .....

2. To filter out the code which was not existed in Table B and show it in the multiple-row card. 

Would you please help me on this ? Appreciate if anyone can help 🙂

Thanks!

[v-alq-msft]

Hi, @Anonymous 

If you take the answer of someone, please mark it as the solution to help the other members who have same problems find it more quickly. If not, let me know and I'll try to help you further. Thanks.

Best Regards

Allan

[v-alq-msft · Accepted solution]

Hi, @Anonymous 

Based on your description, I created data to reproduce your scenario.

A:

B:

Table(a calculated table):

Table = DISTINCT(B[Code])

There is a many-to-many relationship between A and B. You may create a measure as below.

Visual Control = 
IF(
    NOT(SELECTEDVALUE(A[Code]) in DISTINCT('Table'[Code])),
    1,0
)

Finally you may put the measure in the visual level filter to filter out the code which is not existed in Table B and show it in the multiple-row card.

Best Regards

Allan

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

[Anonymous]

if you are looking for a result like this:

then you have only to merge and expand using UI of power query