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.

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.