Tricky count measure

[Anonymous]

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...)

[Anonymous · Accepted solution]

HI @Anonymous,

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