Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 |
---|---|
47 | |
28 | |
23 | |
18 | |
15 |
User | Count |
---|---|
55 | |
34 | |
18 | |
17 | |
15 |