Flag all rows if one row fails for same unique ID

[Anonymous]

I need help with the following (just have put random columns to mimic my situation)

I have the first 4 columns of data and want to insert a fifth column labelled "Fail"

I am struggling to figure out a formula to apply, but want to basically say that if a person (ID) works in multiple departments, and if any of the rows comes back as "at risk ie. below <80% efficiency", then return Y(yes) in the fail column for all instances, even those that have efficiency >80.

Any suggestions?

ID	Deparment	Efficiency (%)	At Risk	Fail
ABCD	1	<80	Y	Y
ABCD	2	<90	N	Y
ABCD	3	<40	Y	Y
DEF	1	<90	N	Y
DEF	2	<60	Y	Y
GHD	1	<90	N	N
GHD	2	>90	N	N

[v-piga-msft · Accepted solution]

Hi @Anonymous ,

To achieve your desired output, please create the two calculated column below.

Please note that you need to change the column type to be Whole number after creating it.

Column = RIGHT('Table1'[Efficiency(%)],2)
Fail =
VAR ATRISK =
    CALCULATE (
        COUNTROWS ( Table1 ),
        FILTER ( Table1, Table1[ID] = EARLIER ( Table1[ID] ) && Table1[Column] <= 80 )
    )
RETURN
    IF ( ATRISK > 0, "Y", "N" )

Here is the output.

Hope this can help you!

Best Regards,

Cherry

[BigDaddyLinda]

Is possible to achieve the same thing in Power Query?

[v-piga-msft · Accepted solution]

Hi @Anonymous ,

To achieve your desired output, please create the two calculated column below.

Please note that you need to change the column type to be Whole number after creating it.

Column = RIGHT('Table1'[Efficiency(%)],2)
Fail =
VAR ATRISK =
    CALCULATE (
        COUNTROWS ( Table1 ),
        FILTER ( Table1, Table1[ID] = EARLIER ( Table1[ID] ) && Table1[Column] <= 80 )
    )
RETURN
    IF ( ATRISK > 0, "Y", "N" )

Here is the output.

Hope this can help you!

Best Regards,

Cherry

[Anonymous · Accepted solution]

Thanks for that response, looks to work.

I just figured out a solution myself

I changed the AtRisk column from N/Y to 0/1

Then created a formula:

Fail = CALCULATE(MAX(Data[At Risk]),ALLEXCEPT(Data,Data[ID]))

This also gave me my desired solution

[Giris]

I'd argue your soluton is better and more elegant.