Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Flag all rows if one row fails for same unique ID

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

2 ACCEPTED SOLUTIONS
v-piga-msft
Resident Rockstar
Resident Rockstar

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.

Capture.PNG

Hope this can help you!Smiley Happy

Best Regards,

Cherry

 

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Anonymous
Not applicable

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

View solution in original post

3 REPLIES 3
v-piga-msft
Resident Rockstar
Resident Rockstar

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.

Capture.PNG

Hope this can help you!Smiley Happy

Best Regards,

Cherry

 

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

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

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.