Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Return1 value for multiple criteria

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).

 

 

Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    7 years ago

    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.

8 Replies

  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    7 years ago

    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's avatar
      Anonymous
      Not applicable
      7 years ago

      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.

  • tex628's avatar
    tex628
    Community Champion
    7 years ago

    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's avatar
      Anonymous
      Not applicable
      7 years ago

      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's avatar
        tex628
        Community Champion
        7 years ago
        Measure = 
VAR OrderNo = SELECTEDVALUE('Table'[OrderNumber])
Return
CALCULATE(
IF(SELECTEDVALUE('Table'[Status]) = "Pass" ; "Pass" ; "Fail" );
ALL('Table');
'Table'[OrderNumber] = OrderNo
)

        Try this instead! :-)

  • AnthonyTilley's avatar
    AnthonyTilley
    Solution Sage
    7 years ago

    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