Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Tricky count measure

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:

Key1Value2Value3
1ABX
1BCX
1CDY
1DEZ

 

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's avatar
    Anonymous
    3 years ago

    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

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable

    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