cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

#### Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors