topic Re: Find 2 matching strings in different rows with same ID? in DAX Commands and Tips

Find 2 matching strings in different rows with same ID?

daxn00b — Wed, 31 Mar 2021 13:23:55 GMT

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!

Re: Find 2 matching strings in different rows with same ID?

Jihwan_Kim — Wed, 31 Mar 2021 13:37:33 GMT

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.

Re: Find 2 matching strings in different rows with same ID?

daxn00b — Wed, 31 Mar 2021 13:55:35 GMT

Jihwan_Kim Wow, thanks for this! Works great!

Re: Find 2 matching strings in different rows with same ID?

Anonymous — Thu, 01 Apr 2021 13:19:34 GMT

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.