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] THE...
  • 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.

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.