Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I have a tricky measure that I don't know how to proceed on.
I have a table with key1 (table A) that is always unique to a person. But this person can have multiple of value2 that can be found in table A and B). I also have value3 (table B) which isn't unique either.
there's a one to many relationship between value2 from both table A & B.
So key1 can have multiple of value2. Value2 can have multiple value3
I want to distinctcount all these value2, except where key1 has value2 where value3 is the same for two rows (or more).
Example:
Key1 | Value2 | Value3 |
1 | AB | X |
1 | BC | X |
1 | CD | Y |
1 | DE | Z |
In the above case I want to count value2 only 3 times. Because it has a value3 that's the same on two rows.
I'm not really sure how to proceed with this... Any pointers in the right direction would be very helpfull, thanks.
(I then also have to apply some other filters in the calc but that should be the easy part...)
Solved! Go to Solution.
HI @thne123,
You can create a measure formula to count values in value3 field based on key1 group:
formula =
CALCULATE (
COUNTROWS ( VALUES ( Table[Value3] ) ),
ALLSELECTED ( Table ),
VALUES ( Table[Key1] )
)
Regards,
Xiaoxin Sheng
HI @thne123,
You can create a measure formula to count values in value3 field based on key1 group:
formula =
CALCULATE (
COUNTROWS ( VALUES ( Table[Value3] ) ),
ALLSELECTED ( Table ),
VALUES ( Table[Key1] )
)
Regards,
Xiaoxin Sheng
User | Count |
---|---|
93 | |
83 | |
77 | |
73 | |
66 |
User | Count |
---|---|
115 | |
105 | |
93 | |
65 | |
60 |