Find 2 matching strings in different rows with same ID?

[daxn00b]

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.

id	color	computer_id
1	red	101
2	red	102
3	red	103
4	green	101
5	green	102
6	yellow	103

Thanks in advance for any help!

[Anonymous]

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.

[daxn00b]

@Jihwan_Kim Wow, thanks for this! Works great!

[Jihwan_Kim · Accepted solution]

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.