Compare two text string same field between two different tables to identify Match/No Match

[toovishant]

Hi Team,

I am trying to compare the Room Name between two tables to Match/No Match. I am using below logic to comapre Room Name field between Table 1 and Table 2 

The below query dosen't seems to provide the desired output. I need which Room Name is same will be "Match" and if not then it will be "No Match"

Validate = VAR _Table1=SELECTEDVALUE(Table1[Column4]) VAR _Table2=SELECTEDVALUE(Table2[Column4]) RETURN if(_Table1=_Table2,"Match","No Match")

Below sample pbix link

https://drive.google.com/file/d/1dKmTcrN1POKeIXIn-7_-3m4wDimblczq/view?usp=drive_link 

Table 1                                            Table 2 

1. Site Code                                  1. Site Code

2. Floor Code                                2. Floor Code

3. Room Code                               3. Room Code

4. Room Name                              4. Room Name

5. Validate                                      5. Validate

Thanks,

[Selva-Salimi]

@toovishant 

did you have any relatioship between these two tables?! from which table did you use column 1, column2, column3 in visual and why?!

[toovishant]

To create a unique column, I need to combine Column(1+2+3) to create a relation between these two Table 1 & Table 2

[mickey64]

For your reference.

Validate = IF(MAX('Table 1'[Column4]) = MAX('Table 2'[Column4]),"Match","No Match")

[toovishant]

@mickey64  - Thanks for the reply. If I appy the above query it gives only "Match"

[mickey64]

Step 0: I use these data below.

Table 1

Table 2

Step 1: I add a relationship.

Step 2:　I make a measure.

Validate = IF(MAX('Table 1'[Column4]) = MAX('Table 2'[Column4]),"Match","No Match")

Step 3: I make a matrix and a table.

Matrix

Table

[toovishant]

Sorry, In this sample file column 3 has limited data, it cannot be a unique coumn, because each site may have same room will have same room code accross the floor. So to get a unique column need to add these three Column (1+2+3) (Site+Floor Code+Room Code)

[mickey64]

For your reference.

I add a "Key_Column" to the each table and change a relationship.

[toovishant]

when I use larger set of data, this query is not working.

[mickey64 · Accepted solution]

For your eference.

[Selva-Salimi]

Hi @toovishant 

in this case it seems that lookupvalue can perform better. then you can write as follows:

Validate =
VAR _Table2RoomName = LOOKUPVALUE(Table2[Column4], Table2[Column4], Table1[Column4])
RETURN IF(
Table1[Column4] = _Table2RoomName,
"Match",
"No Match"
)

If this post helps, then I would appreciate a thumbs up and mark it as the solution to help the other members find it more quickly. 

[toovishant]

@Selva-Salimi  - Thanks for the reply 

when i use lookupvalue it doesn't recognize Table1[column4] or vise versa. Please can you show by using the sample table and execute this query.