CODEWITHSUNDEEP
powerbidax
dkackman
dkackman

Reputation: 15579

How do I count the number items that match criteria across multiple rows?

I've got a data-set such as:

OrderNumber  PassFail
12345        fail
12345        pass
23456        pass
34567        pass

In Dax/PowerBI I want a measure that is distinct count of OrderNumbers that have with no fail records. So in the example above, that number would be 2: 12345 does not meet the criteria but the other two do.

Upvotes: 0

Views: 510

Answers (2)

smpa01
smpa01

Reputation: 4346

measure=
countrows(
  except(
    summarize(
      filter(tbl,tbl[PassFail]="pass"),
      tbl[OrderNumber]
    ),
    summarize(
      filter(tbl,tbl[PassFail]="fail"),
      tbl[OrderNumber]
    )
  )
)

Upvotes: 0

mkRabbani
mkRabbani

Reputation: 16918

You can check this measure-

pass_order_count = 

var table_fail = 
SELECTCOLUMNS(
    FILTER(
        your_table_name,
        your_table_name[PassFail] = "Fail"
    ),
    "OrderNumber" ,[OrderNumber]
)

var table_pass = 
EXCEPT(
    DISTINCT(your_table_name[OrderNumber]),
    table_fail
) 

return COUNTROWS(table_pass)

Upvotes: 1

Related Questions