Return1 value for multiple criteria

[Anonymous]

Hi there,

Been stuck on this for a while and appreciate any input;

I've a list of sales orders, with each order comprising of multiple order lines, and each order line can be pass or fail. 

However, I report based on the pass/fail status of orders, and if just one line fails, the whole order fails.

Example: 1 order number has 6 lines; 2 fail, 4 pass, but the order overall is a fail

How do I return a single pass/fail for each order? 

In excel I would sort by Pass/Fail column with fail on top, pass below, then remove duplicate sales order #'s (as it'd clear from top down, removing duplicate sales orders, and any duplicate "pass" results would be removed if a sales order with "fail" was found earlier in the column).

[v-juanli-msft · Accepted solution]

Hi @Anonymous 

For this test data, create two measures

fail n = CALCULATE(COUNT(Sheet5[status]),FILTER(ALLEXCEPT(Sheet5,Sheet5[order]),Sheet5[status]="fail"))

flag = IF([fail n]>0,"fail","pass")

Add them in a table visual

Best Regards

Maggie

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

[Anonymous]

Hi @v-juanli-msft Maggie, 

Would you be able to convert that code; 

Failed Orders = CALCULATE(COUNT('PD - Line'[Order Fail PD2]),FILTER(ALLEXCEPT('PD - Line','PD - Line'[Sales document]),'PD - Line'[Order Fail PD2]="Fail"))

Into a line that can be used within a custom column creation formula in PowerQuery? It works a dream in DAX, but need in PowerQuery to resolve an issue i'm having with remove duplicates (it's not consistent in it's choices), so think order level pass/fail would resolve over the contradictory line level pass/fail as the orders it's marking as pass when removing duplicates has both passing & failing lines, and it's choosing pass over fail despite ranking and sorting to stop this.

[AnthonyTilley]

I had a similar issue and just used a Max value to get the overall status in a matrix.

i assinged a value of 1 for pass and 2 for fail and then had a measure of

Maxstatus =Max(Orders[STATUS CODE])

when used in a matrix with Order id as the row header and the measure above means that if any line exsists with a fail the max value will be a 2. if there are no fail lines and all lines are pass then the max value will be 1

[tex628]

With a measure you should be able to do it with simply: 

Measure = IF(SELECTEDVALUE('Table'[Status]) = "Pass" ; "Pass" ; "Fail" )

Put it in a matrix with your orders and it should return fail on all orders that have fails.

[Anonymous]

Unfortunately this shows which order line is pass or fail, but doesn't class an order as a whole as pass/fail (each order can contain both pass and fail lines, but if so, the order is failed).

[tex628]

Measure = 
VAR OrderNo = SELECTEDVALUE('Table'[OrderNumber])
Return
CALCULATE(
IF(SELECTEDVALUE('Table'[Status]) = "Pass" ; "Pass" ; "Fail" );
ALL('Table');
'Table'[OrderNumber] = OrderNo
)

Try this instead! 🙂

[Anonymous]

this didn't work off the bat, and after changeing the last 2 semicolons with commas, it worked, but just gives a pass result, and no fails

[tex628]

@Anonymous Are you referring to my code?