Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
Anonymous
Not applicable

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
OwenAuger
Super User
Super User

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 


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

View solution in original post

2 REPLIES 2
OwenAuger
Super User
Super User

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 


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn
Anonymous
Not applicable

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!

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors