cancel
Showing results for
Did you mean:
Frequent Visitor

## Calculate the number of duplicate values in two columns

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.

1 ACCEPTED SOLUTION
Super User I

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

Owen Auger

My Blog
2 REPLIES 2
Super User I

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

Owen Auger

My Blog
Frequent Visitor

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!

Announcements