Forum Discussion

Rushil_15's avatar
Rushil_15
Frequent Visitor
2 years ago
Solved

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 tabl...
Need Help
  • speedramps's avatar
    speedramps
    2 years ago

    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 Table3

     

    In Table3 Split Column by Delimter 
    with Comma,  Each occurence, Advanced and Rows

     

    Create 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 measure
     

     

    Thanks 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. 



     

audreygerred's avatar
audreygerred
Super User
2 years ago

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: 

 

Rushil_15's avatar
Rushil_15
Frequent Visitor
2 years ago

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)

  • audreygerred's avatar
    audreygerred
    Super User
    2 years ago

    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"
      )