topic Re: Calculate the number of duplicate values in two columns in DAX Commands and Tips

Calculate the number of duplicate values in two columns

Anonymous — Wed, 13 Nov 2019 17:50:24 GMT

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.

Re: Calculate the number of duplicate values in two columns

OwenAuger — Thu, 14 Nov 2019 00:49:50 GMT

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

Re: Calculate the number of duplicate values in two columns

Anonymous — Mon, 18 Nov 2019 15:46:23 GMT

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!