Calculate the number of duplicate values in two columns

[Anonymous]

I am having issues creating a measure that counts the number of duplicate values in two columns. My initial idea was to create two calculated columns and then join them on the duplicate values. I then could count the number of rows and determine how many there were. However, my measure is not returning a possible value.

Here is an example of what I'm trying to do:

Column 1:     Column 2: 

1                    1

2                    6

3                    7

4                    3

5                    9

The measure I need would return 2 since there are two duplicate values (1 and 3) in these two columns.

Any assistance on creating such a measure would be helpful.

[OwenAuger · Accepted solution]

Hi @Anonymous 

Something like this should do what you want:

Common Value Count = 
COUNTROWS ( 
    INTERSECT (
        VALUES ( YourTable[Column 1] ),
        VALUES ( YourTable[Column 2] )
    )
)

VALUES would include blank values that are generated if either of these columns is on the 1-side of a strong relationship that happens to be invalid. To exclude these use DISTINCT instead.

Regards,

Owen 

[Anonymous]

Thanks for the post! The measure worked perfectly as intended. I only modified the measure by adding a filter. I forgot to mention this need in my original post. You can see the final measure I ended up using in the post below.

Common Value Count With Filter = COUNTROWS(
    INTERSECT(
        CALCULATETABLE(VALUES(YourTable[Column 1]), FILTER(YourTable, YourTable[Column 1] = "itemOne")), 
        CALCULATETABLE(VALUES(YourTable[Column 2]), FILTER(YourTable, YourTable[Column 2] = "itemTwo"))
    )
)

Thanks again!