cancel
Showing results for 
Search instead for 
Did you mean: 
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
Ailsa-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
Ailsa-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

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

 

 

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
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!

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
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!