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 !! YouTube Channel !! Connect on Linkedin

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
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!