COMPARING 2 COLUMNS AND FIND A MATCH ON ITS DATA

[RanHo]

Good day Guys!

               I just want to ask how I can compare 2 columns to find the matching data  in same table
Here's my example: 
As you can see I have 2 columns if the data on COLUMN1 has matched on COLUMN2 then it counts. 

Thanks in advance!

~Ran

[v-shex-msft]

Hi @RanHo,

I test with a sample formula that is similar to 'johnt75' provided and it work well, did any whitespace or special character in your fact table records?

Can you please some dummy data that keep the raw data structure and paste it here in table format? It should help us clarify your scenario and test to coding formula.

How to Get Your Question Answered Quickly  

Regards,

Xiaoxin Sheng

[RanHo]

@v-shex-msft  Yes I already accepted it as a solution. My bad self error, but it's solved now thanks.

[johnt75 · Accepted solution]

You can try

Num matches =
COUNTROWS (
    INTERSECT ( VALUES ( 'Table'[Column1] ), VALUES ( 'Table'[Column2] ) )
)

[RanHo]

@johnt75  My bad, I retry your measure and it smoothly worked. Thanks a lot.

[RanHo]

@johnt75  I already try it but unfortunately it's not working the output was blank. But thanks for reply.

~Ran

[Anonymous]

Hi @RanHo ,

This is not the best solution but should produce the output you are looking for. Assuming you have 1 table below:

Step 1: Create a calculated table using " Column 2"

Table = SUMMARIZE(Sheet1,Sheet1[Column2])

Step 2: Build a relationship between the two tables 

Step 3: Create a new measure

Measure = 

CALCULATE(
    COUNT(Sheet1[Column1]),
    FILTER(Sheet1,Sheet1[Column1] = RELATED('Table'[Column2])
    )
)

Step 4: Visualize

BR