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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors