Forum Discussion

daxn00b's avatar
daxn00b
Frequent Visitor
5 years ago
Solved

Find 2 matching strings in different rows with same ID?

Hello Everyone,

 

I'm trying to figure out how to get the count of unique computer_ids that have 2 different strings in different rows. For example, if computer_id = color[red] AND color[green] THEN count 1. The example below should equate to a count of 2.

 

idcolorcomputer_id
1red101
2red102
3red103
4green101
5green102
6yellow103

 

Thanks in advance for any help!

  • Jihwan_Kim's avatar
    Jihwan_Kim
    5 years ago

    Hi, daxn00b 

     

    Please try the below measure.

     

    Red and Green =
    VAR redcom =
    SUMMARIZE ( FILTER ( ALL ( Data ), Data[color] = "red" ), Data[computer_id] )
    VAR greencom =
    SUMMARIZE ( FILTER ( ALL ( Data ), Data[color] = "green" ), Data[computer_id] )
    RETURN
    COUNTROWS ( INTERSECT ( redcom, greencom ) )

     

    Hi, My name is Jihwan Kim.

    If this post helps, then please consider accept it as the solution to help other members find it faster.

3 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    5 years ago

    Hi, daxn00b 

     

    Please try the below measure.

     

    Red and Green =
    VAR redcom =
    SUMMARIZE ( FILTER ( ALL ( Data ), Data[color] = "red" ), Data[computer_id] )
    VAR greencom =
    SUMMARIZE ( FILTER ( ALL ( Data ), Data[color] = "green" ), Data[computer_id] )
    RETURN
    COUNTROWS ( INTERSECT ( redcom, greencom ) )

     

    Hi, My name is Jihwan Kim.

    If this post helps, then please consider accept it as the solution to help other members find it faster.

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi,
    You can also try the following measure to get the same result.
    ResultCount =
    COUNTROWS(
    INTERSECT(
    CALCULATETABLE(VALUES(Data[computer_id]),Data[Color] = "green"),
    CALCULATETABLE(VALUES(Data[computer_id]),Data[Color] = "red")
    ))

    Same method, I have implemented in the following video.