Compare 3 column of 3 different table.

[kannanAhammed]

Hello,

I have 3 table A, B and C one all have the field AssectName

I would like somthing like below.

AssectName Combined	AssectName A	AssectName B	AssectName C
Name 1	Yes	No	No
Name 2	No	Yes	No
Name 3	No	No	Yes

I have combined 3 table using the below dax funcion with help from the community

newtable =

SUMMARIZE (

UNION ( VALUES ( 'A'[AssectName] ), VALUES ( 'B'[AssectName]), 'C 'Assets (Computers)'[AssectName]) ),

[AssectName]

How to I 3 columns to where AssectName A checks if the "Name 1"is present in the table "A" if so the add "Yes" if not add "No"

[v-yetao1-msft · Accepted solution]

Hi @kannanAhammed ,

Please correct me if I wrongly understood your question.

I have combined the three tables with dax .

Table = DISTINCT(UNION(ALL(A[AssectName]),ALL(B[AssectName]),ALL(C[AssectName])))

Then add three columns in combined table , like this:

AssectName A = SWITCH(TRUE(),'Table'[AssectName] in VALUES('A'[AssectName]),"Yes","No")

AssectName B = SWITCH(TRUE(),'Table'[AssectName] in VALUES('B'[AssectName]),"Yes","No")

AssectName C = SWITCH(TRUE(),'Table'[AssectName] in VALUES('C'[AssectName]),"Yes","No")

The effect is as shown:

Best Regards

Community Support Team _ Ailsa Tao

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

[kannanAhammed]

I was able to get it to work with this 

AssectName A= if(ISBLANK(LOOKUPVALUE('A'[AssectName],'A'[AssectName],newtable[AssectName])),"No","Yes")

[kannanAhammed]

Thanks Alisa, 

This is what I am looking for but I am TableA is not comming up in the expression. 

AssectName A = SWITCH(TRUE(),'TableA'[AssectName] in VALUES('A'[AssectName]),"Yes","No")

What i was trying is right clicking on the CombinedTable (newly created) -> add new column -> and add the dax function you have shown. 

But the only referance to the table that is shown is CombinedTable created via below expression

CombinedTable=

SUMMARIZE (

UNION ( VALUES ( 'A'[AssectName] ), VALUES ( 'B'[AssectName]), 'C 'Assets (Computers)'[AssectName]) ),

[AssectName]

[amitchandak]

@kannanAhammed , Not very clear. You need create a bridge/dimension table

new Table= distinct(union(distinct(TableA[AssectName ]),distinct(TableB[AssectName ]),distinct(TableC[AssectName ])))

or

New Table =

distinct(union(all(TableA[AssectName ]),all(TableB[AssectName ]),all(TableC[AssectName ])))

Also Check

https://www.youtube.com/watch?v=Bkf35Roman8

[kannanAhammed]

Thank you, but I have already done that.

Now what i need is to create 3 fields with yes or no to show if the value in "AssectName Combined" exist is in Table A, or Table B or Table C.  If exists then "Yes" if not them "No". see below for details.

AssectName Combined	AssectName A	AssectName B	AssectName C
Name 1	Yes	No	No
Name 2	No	Yes	No
Name 3	No	No	Yes