How to display Yes if condition matches for all duplicate ids

[hpatel24779]

Hi all,

reposting this as solution given is not what is intended.

I need some help to figure this out. in my table, i have ID and Step Type. Each id can have multiple steps. i want to show yes for each duplicate id if it matches my condition. for example i have the following:

ID	Step Type
1	A
1	C
2	F
3	B
3	C
4	C

I want to add a column that if step = C then it shows "yes" for each ID and "No" if it doesn't.

Expected result should look like this:

ID	Step Type	Result
1	A	Yes
1	C	Yes
2	F	No
3	B	Yes
3	C	Yes
4	C	Yes

Can someone please help with this. Please note, if there is only 1 id and step type = C then it should also show Yes

Thanks

Hetal

[Bibiano_Geraldo · Accepted solution]

Hi @hpatel24779 ,

You can achieve your goal by this measure using DAX:

Result = 
IF(
    CALCULATE(
        COUNTROWS(TableName),
        TableName[Step Type] = "C",
        ALLEXCEPT(TableName, TableName[ID])
    ) > 0,
    "Yes",
    "No"
)

Now your Matrix should look like this:

[hpatel24779]

thank you so much @Bibiano_Geraldo this is what i needed

[Bibiano_Geraldo]

Hi @hpatel24779 , Happy it worked.

[johnt75]

You can use

Includes step C = 
VAR AllSteps =
    CALCULATETABLE ( VALUES ( 'Table'[Step Type] ), REMOVEFILTERS ( 'Table'[Step Type] ) )
VAR Result =
    IF ( "C" IN AllSteps, "Yes", "No" )
RETURN
    Result

[ribisht17]

@hpatel24779 

Use this DAX

Check Dup =

            CALCULATE(COUNT(Q1_Duplicate[Step Type]),Q1_Duplicate[ID]=EARLIER(Q1_Duplicate[ID]),all(Q1_Duplicate))

Check Dup =

           var cnt= CALCULATE(COUNT(Q1_Duplicate[Step Type]),Q1_Duplicate[ID]=EARLIER(Q1_Duplicate[ID]),all(Q1_Duplicate))

           var t_f =IF(cnt>1,1, if(Q1_Duplicate[Step Type]="C",1,0))

           RETURN

           IF(t_f=1,1,0)

For Yes and No format please use

Check Dup =

           var cnt= CALCULATE(COUNT(Q1_Duplicate[Step Type]),Q1_Duplicate[ID]=EARLIER(Q1_Duplicate[ID]),all(Q1_Duplicate))

           var t_f =IF(cnt>1,1, if(Q1_Duplicate[Step Type]="C",1,0))

           RETURN

           IF(t_f=1,"Yes","No")

Regards,

Ritesh