Skip to main content
cancel
Showing results for 
Search instead 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

Reply
kannanAhammed
Helper IV
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 CombinedAssectName AAssectName BAssectName C
Name 1YesNoNo
Name 2NoYesNo
Name 3NoNoYes

 

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

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:

Ailsa-msft_0-1617845134507.png

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.

View solution in original post

5 REPLIES 5
v-yetao1-msft
Community Support
Community Support

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:

Ailsa-msft_0-1617845134507.png

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.

I was able to get it to work with this 

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

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

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

 

 

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 CombinedAssectName AAssectName BAssectName C
Name 1YesNoNo
Name 2NoYesNo
Name 3NoNoYes

 

 

 

 

Helpful resources

Announcements
Europe Fabric Conference

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.

July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

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