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

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! :-)

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      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's avatar
        tex628
        Community Champion
        7 years ago

        Anonymous Are you referring to my code?