Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe 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.
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.
Solved! Go to Solution.
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
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
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!
User | Count |
---|---|
20 | |
20 | |
14 | |
10 | |
8 |
User | Count |
---|---|
29 | |
28 | |
12 | |
12 | |
12 |