Forum Discussion
Need Help on Matching columns from Different Tables
Hello,
So I am trying to get a column that shows if the reason in two columns both in different tables matches or not. I did many to many relationships using the IDs. Below is an example of two tables where the reason column has multiple string values and im looking if any of them match it will show as match
Thanks for your help
Try this solution ..
https://1drv.ms/u/s!AgMTUY3Uvq3bg8glg-ytnTIQ5iTN9Q?e=CGYbXv
How it works ....
Add a "Table" column = 1 to Table1
Add a "Table" column = 2 to Table2
Append Table1 and Table2 to new Table3In Table3 Split Column by Delimter
with Comma, Each occurence, Advanced and RowsCreate measures
Reasons =CALCULATE(CONCATENATEX(DISTINCT(Table3[Reason]),Table3[Reason],", "))Answer =SWITCH(TRUE(),[Reasons] = BLANK(),"N/A",COUNTROWS(Table3) > DISTINCTCOUNT(Table3[Reason]),"Yes","No")
The on a new page, drag a table visual, and drag Tabl3 ID, the Reasons measure and the Answer measureThanks for the clear description of the problem with example data. I wish everyone did that!
Remember we are unpaid volunteers. So please click the thumbs up and the accept as solution button to leave kudos.
If you give someone a fish then you only give them one meal, but if you teach them how to fish then they can feed themselves and teach others for a lifetime. This is how I try answer Power BI and DAX questions with clear examples so your can learn techniques.
One question per ticket please. If you need to extend your request then please raise a new ticket.
You will get a quicker response and each volunteer solver will get the kudos they deserve. Thank you !
If you quote @speedramos in your next tickers then I will then receive an automatic notification, and will be delighted to help you again.
Please click the thumbs up and the accept as solution button.
11 Replies
Try this solution ..
https://1drv.ms/u/s!AgMTUY3Uvq3bg8glg-ytnTIQ5iTN9Q?e=CGYbXv
How it works ....
Add a "Table" column = 1 to Table1
Add a "Table" column = 2 to Table2
Append Table1 and Table2 to new Table3In Table3 Split Column by Delimter
with Comma, Each occurence, Advanced and RowsCreate measures
Reasons =CALCULATE(CONCATENATEX(DISTINCT(Table3[Reason]),Table3[Reason],", "))Answer =SWITCH(TRUE(),[Reasons] = BLANK(),"N/A",COUNTROWS(Table3) > DISTINCTCOUNT(Table3[Reason]),"Yes","No")
The on a new page, drag a table visual, and drag Tabl3 ID, the Reasons measure and the Answer measureThanks for the clear description of the problem with example data. I wish everyone did that!
Remember we are unpaid volunteers. So please click the thumbs up and the accept as solution button to leave kudos.
If you give someone a fish then you only give them one meal, but if you teach them how to fish then they can feed themselves and teach others for a lifetime. This is how I try answer Power BI and DAX questions with clear examples so your can learn techniques.
One question per ticket please. If you need to extend your request then please raise a new ticket.
You will get a quicker response and each volunteer solver will get the kudos they deserve. Thank you !
If you quote @speedramos in your next tickers then I will then receive an automatic notification, and will be delighted to help you again.
Please click the thumbs up and the accept as solution button.
Hey, Thanks for the code and i think most of this is working but in some cases reason is not matching but still shows answer as YES
Please share pbix via with the example problem. So I can check it.
Also please click thumbs up to give kudos. Thanks.
Note your previous examples had lots of typing mistakes aggression and agression, also there were trailing spaces.
We want to help you but your description is too vaugue. Please write it again clearly.
Please DON'T copy & paste your DAX that does not work and expect us to fathom what you want to do. That would be crazy. 😀Please just give a simple non technical functional decscription of what you want, then let us suggest the DAX. Thank you.
Provide example input data as table text (not a screen print) so we can import the data to build a solution for you.
Also provide the example desired output, with a clear description of the process flow.
Remember not to share private data ... we don't want you to get into trouble. 😧
Take time and care to use the same table and field names in the input, output and description so we can understand your problem and help you.You will get a quick response if you put time, care and effort into writing clear problem descriptions.
Vaugue descriptions can waste your time and ourtime.
Look foward to helping you when the above information is forthcoming
We're sorry, butyou have not given me acess to your to sharepoint Trty using OneDrive or Dropbox and remember to grant share access
Hello! I create two tables like you have above (as well as a 5th row where the values do match in both tables) and joined on ID.
Table1
Table 2
In Table 1, I added the below calculated column:
Match = EXACT(Table1[Reason],RELATED(Table2[Reason]))The results look like below:Hey, Thanks for your quick reply I think this one is working but is there any way we can get "TRUE" even if one of them matches like ID 1 has Reason A,B,C in Table1 and ID 1 Has Reason A in Table 2 but Match shows "True" (it doesn't have to be an exact match)
How about this - it looks for the text then lets you know which one it found:
Found in Table2. =SWITCH(TRUE(),SEARCH("A", RELATED(Table2[Reason]),1, 0) > 0, "A",SEARCH("B", RELATED(Table2[Reason]),1, 0) > 0, "B",SEARCH("C", RELATED(Table2[Reason]),1, 0) > 0, "C",SEARCH("D", RELATED(Table2[Reason]),1, 0) > 0, "D","not found")
Please find the attached and thanks😀
