Count distinct unique ID based on multiple row condition and column

Question

How to create measure to count DISTINCT IDs from multiple criteria from specific column? Below are sample dataset and the condition to create measures.

Criteria: How many unique ID that flows to the prevstepid and stepid using the below condition.

CountID Measure 1 = 
Distinctcount ID if prevstepid = "Proc1" and stepid="Verify" and 
prevstepid="Verify" and stepid="Fail" 

CountID Measure 2 = Distinctcount ID  if prevstepid = "Proc1" and 
stepid="Verify" and 
prevstepid="Verify" and stepid="Reg2" and prevstepid="Reg2" and 
stepid="Test2" 

CountID Measure 3 = Distinctcount ID  if prevstepid = "Proc1" and 
stepid="Verify" and 
prevstepid="Verify" and stepid="Proc3" and prevstep="Proc3" and 
stepid="Pass" 

I tried this code and its not working.

Measure 1 = 
 CALCULATE (
 DISTINCTCOUNT ( table[id] ), 
 FILTER (table, table[prevstepid] = "proc1" && table[stepid]="Verify" 
 && table[prevstepid]= "Verify" && table[stepid]="Fail")) 

Thank you.

Answer 1

Maybe this makes more sense:

Measure 1 = 
 CALCULATE (
 DISTINCTCOUNT ( table[id] ), 
 FILTER ( table, ( table[prevstepid] = "proc1" && table[stepid]="Verify" )
 || ( table[prevstepid]= "Verify" && table[stepid]="Fail") ) )

I hope you get the idea. Your current Measure 1 is always filtered out, because e.g. you ask for both states at the same time: e.g. “proc1” and “verify”, which none of the rows can fulfill.

In case you want to check both cases are fulfilled, you may want to define e.g. a calculated column to check these 2 conditions and check this helper columns result.

Edit:

For the helper column1:

helperColumn1 = 
 CALCULATE (
    COUNTROWS ( table ), 
    ALLEXCEPT ( table, table[id] ),
    ( table[prevstepid] = "proc1" && table[stepid]="Verify" )
    || ( table[prevstepid]= "Verify" && table[stepid]="Fail") 
  )

and for measure1:

measure1 = 
CALCULATE (
   DISTINCTCOUNT ( table[id] ),
   table[helperColumn1] = 2
)

You can do the same way for the measure2 and measure3.