Occurence within a category

[Antmkjr]

ID	Category	Expected Output
1	A	1
1	B	1
1	C	1
2	B	0
2	C	0
2	D	0
3	A	1
3	B	1
4	A	0
4	A	0
4	A	0

I have a table like above , I need to find if a particular ID has "A" AND another category, then expected output is 1.

If ID has category A only , then 0. Also if no Category A , then also expected output is 0

[Jihwan_Kim · Accepted solution]

Hi,

Please check the below picture and the attached pbix file.

It is for creating a new column.

Expected output CC =
VAR _condition =
    SUMMARIZE ( FILTER ( Data, Data[ID] = EARLIER ( Data[ID] ) ), Data[Category] )
RETURN
    IF ( COUNTROWS ( _condition ) > 1 && "A" IN _condition, 1, 0 )