Forum Discussion
Check if column value between 2 tables is equal
Hi all,
Currenlty I am trying to compare values from two columns between two tables.
The situation and desired outcome is as follows:
Table A:
| Country | Currency |
| United Arab Emirates | United Arab Emirates dirham |
| Afghanistan | Afghanistani afghani |
| Albania | Albanian lek |
Table B:
| Country | Currnecy |
| United Arab Emirates | United States Dollar |
| Afghanistan | Afghanistani afghani |
| Albania | Albanian lek |
I want to create a new column with the desired outcome being as follows:
IF
Table A [country] + [currency] = Table B [country] + [currency] THEN 'Government Bond'
ELSE 'Coprorate Bond'
I cannot seem to get the condtionality to work. My main struggle is how to concatenate and compare the values between the tables. In my model I have joined the two tables on Country, so the relation is there. I have tried using the IF function and Switch, but those didn't seem to work (with my level of knowledge). Can someone please help me to figure this out?
Any help is much appreciated.
Thanks!
Regards,
Juuls
Hi Juuls ,
If you are creating a measure,remember to add "Max" when you wanna query a certain field.
Modify your measure as below:
Measure = IF(ISBLANK(COUNTX(FILTER(ALL('Table A'),'Table A'[Country]=MAX('Table B'[Country])&&'Table A'[Currency]=MAX('Table B'[Currnecy])),'Table A'[Country])),"Coprorate Bond" ,"Government Bond")And you will see:
If you need a calculated column,using below dax expression:
Column = IF('Table A'[Country]&'Table A'[Currency]=RELATED('Table B'[Country])&RELATED('Table B'[Currnecy]),"Government Bond","Coprorate Bond")And you will see:
For the related .pbix file,pls see attached.
Best Regards,
KellyDid I answer your question? Mark my post as a solution!
5 Replies
- amitchandak
Super User
Juuls , new column in TableA
if(isblank(countx(filter(TableB, TableB[Country] = tableA[Country] && TableB[Currnecy] = tableA[Currnecy]),TableB[Country])), "Coprorate Bond" ,"Government Bond")
or new column in TableB
if(isblank(countx(filter(tableA, TableB[Country] = tableA[Country] && TableB[Currnecy] = tableA[Currnecy]),tableA[Country])), "Coprorate Bond" ,"Government Bond")
First of all, thanks for the reply!
Whenever I am trying to refer to the second table, the DAX shows me the table/column cannot be found. I have added a screenshot from the statement that I wrote down and the notifications I receive. Additionally I have added a screenshot from the relational model, which automatically shows a many to many relationship. Might this be the/an issue?
v-kelly-msftCommunity Support
Hi Juuls ,
If you are creating a measure,remember to add "Max" when you wanna query a certain field.
Modify your measure as below:
Measure = IF(ISBLANK(COUNTX(FILTER(ALL('Table A'),'Table A'[Country]=MAX('Table B'[Country])&&'Table A'[Currency]=MAX('Table B'[Currnecy])),'Table A'[Country])),"Coprorate Bond" ,"Government Bond")And you will see:
If you need a calculated column,using below dax expression:
Column = IF('Table A'[Country]&'Table A'[Currency]=RELATED('Table B'[Country])&RELATED('Table B'[Currnecy]),"Government Bond","Coprorate Bond")And you will see:
For the related .pbix file,pls see attached.
Best Regards,
KellyDid I answer your question? Mark my post as a solution!
Hello,
Create new column in Table1,Column = IF(Table1[Country]&Table1[Currency]=Table1[Country]&RELATED(Table2[Currnecy]),"Government Bond","Coprorate Bond")
or Create new column in Table2,
Column = IF(Table2[Country]&Table2[Currnecy]=Table2[Country]&RELATED(Table1[Currency]),"Government Bond","Coprorate Bond")
