Distinct count of a field based on Sequence of Process - DAX

[RRaj_293]

Hello , 

I want to count the distinct IDs which have completed the process cycle which is from 'New Email' to 'Complete' only. Other IDs with an incomplete process cycle which does not End with a 'Complete' or start with 'New Email'  needs to be ignored. 

ID	Process Status	Seq
11A	NEW EMAIL	10
11A	PROCESSING	20
11A	PROCESSING TRANSFORMATION	30
11A	PROCESSING TRANSFORMATION	30
11A	PROCESSING TRANSFORMATION	30
11A	PROCESSING OUTPUT	40
11A	VALIDATING	50
11A	WORKQUEUE	70
11A	COMPLETE	100
12A	NEW EMAIL	10
12A	PROCESSING	20
12A	PROCESSING TRANSFORMATION	30
12A	PROCESSING TRANSFORMATION	30
12A	PROCESSING TRANSFORMATION	30
12A	PROCESSING OUTPUT	40
12A	VALIDATING	50
12A	WORKQUEUE	70
12A	COMPLETE	100
13B	NEW EMAIL	10
13B	PROCESSING	20
13B	TERMINATING	1000
14C	NOTIFICATION	99
15D	NEW EMAIL	10
15D	PROCESSING	20

In the above example Only IDs - 11A and 12A have completed the process hence I need a count of 2 as my result.

Tried this which didn't work!

Test_Msr = CALCULATE(Distinctcount(ID) , FILTER( Min(seq = 10) && Max(seq = 100))) 

Can someone please help me with the dax!

[bhanu_gautam · Accepted solution]

@RRaj_293 , Try using below DAX

Test_Msr =
CALCULATE(
DISTINCTCOUNT('Table'[ID]),
FILTER(
'Table',
'Table'[ID] IN
SELECTCOLUMNS(
FILTER(
'Table',
'Table'[Process Status] = "NEW EMAIL" && 'Table'[Seq] = 10
),
"ID", 'Table'[ID]
) &&
'Table'[ID] IN
SELECTCOLUMNS(
FILTER(
'Table',
'Table'[Process Status] = "COMPLETE" && 'Table'[Seq] = 100
),
"ID", 'Table'[ID]
)
)
)

[RRaj_293]

Thanks @ThxAlot  and @bhanu_gautam  Issue is resolved. 

[ThxAlot · Accepted solution]

[bhanu_gautam · Accepted solution]

@RRaj_293 , Try using below DAX

Test_Msr =
CALCULATE(
DISTINCTCOUNT('Table'[ID]),
FILTER(
'Table',
'Table'[ID] IN
SELECTCOLUMNS(
FILTER(
'Table',
'Table'[Process Status] = "NEW EMAIL" && 'Table'[Seq] = 10
),
"ID", 'Table'[ID]
) &&
'Table'[ID] IN
SELECTCOLUMNS(
FILTER(
'Table',
'Table'[Process Status] = "COMPLETE" && 'Table'[Seq] = 100
),
"ID", 'Table'[ID]
)
)
)