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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Power BI Header Change

Power BI Header Update

Read about the exciting updates for the Power BI forum.

Top Solution Authors