For each duplicate id, show yes if it matches condition

[hpatel24779]

Hi all,

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

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

Can someone please help with this.

Thanks

Hetal

[Ritaf1983 · Accepted solution]

Hi @hpatel24779 
You can use DAX formula:

Flag =

var Occurrences =

CALCULATE(

    COUNTROWS('Table'),

    FILTER('Table', 'Table'[ID] = EARLIER('Table'[ID]))

)

RETURN

if(Occurrences>1, "Yes","No")

The pbix is attached

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

[hpatel24779]

thank you so much @Ritaf1983 

this is exactly what i was after. much appreciated

[Ritaf1983]

Hi @hpatel24779 

Did you download the PBIX file I attached and try to compare it with what you're doing?
If not, it's worth downloading and checking.
If you did, and it’s not working, it probably means the script is not exactly as described in the question.
Please save your PBIX file to some public cloud and share it via a link, excluding sensitive data.

[hpatel24779]

Sorry jumped the gun, this is actually not working as intended

[Kedar_Pande]

@hpatel24779 

Create a calculated column

Result = 
IF(
CALCULATE(
COUNTROWS(FILTER('Table', 'Table'[ID] = EARLIER('Table'[ID]) && 'Table'[Step Type] = "C")),
ALLEXCEPT('Table', 'Table'[ID])
) > 0,
"Yes",
"No"
)

💌 If this helped, a Kudos 👍 or Solution mark would be great! 🎉
Cheers,
Kedar
Connect on LinkedIn

[hpatel24779]

Hi @Kedar_Pande, i tried your method but when entering the line countrows(filter('Table','Table'[ID] = Earlier('Table'[ID], i don't see the table after earlier( if this makes sense?

[Kedar_Pande]

Please share a simplified version of your PBIX file (in English) without sensitive data. You can upload it to a public cloud service like OneDrive, Google Drive, or Dropbox and share the link. This will help in understanding your data structure and the issue, allowing for more precise guidance.