cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Warthog710
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
OwenAuger
Super User I
Super User I

Hi @Warthog710 

 

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!

My Blog
Connect on Twitter
Connect on LinkedIn

View solution in original post

2 REPLIES 2
OwenAuger
Super User I
Super User I

Hi @Warthog710 

 

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!

My Blog
Connect on Twitter
Connect on LinkedIn

View solution in original post

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
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Get Ready for Power BI Dev Camp

Power BI Dev Camp - June 24th

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors