cancel
Showing results for
Did you mean:  Helper IV

## Compare 3 column of 3 different table.

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"

1 ACCEPTED SOLUTION  Community Support

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.

5 REPLIES 5  Community Support

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.  Helper IV

I was able to get it to work with this

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

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]  Super User

@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

Dashboard of My Blogs !! Connect on Linkedin

Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!  Helper IV

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  