Count distinct field based on process Sequence

[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!

[Anonymous · Accepted solution]

Hi @RRaj_293 ,

I have tested Selva-Salimi's workaround, it will return a error due to GroupBY.

I think you can try code as below to create a measure.

CompletedCycleIDs = 
VAR _FILTER = FILTER(ProcessData,ProcessData[Process Status] IN {"NEW EMAIL","COMPLETE"})
VAR _SUMMARIZE = SUMMARIZE(_FILTER,[ID],"COUNT", 
VAR _ID = [ID]
RETURN
COUNTAX(FILTER(_FILTER,[ID] = _ID),[ID]))
RETURN
COUNTX(FILTER(_SUMMARIZE,[COUNT] = 2),[ID])

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Selva-Salimi]

Hi @RRaj_293 

you can write it as follows:

CompletedCycleIDs =
CALCULATE(
DISTINCTCOUNT(ProcessData[ID]), 
FILTER(
GROUPBY(
ProcessData,
ProcessData[ID],
"FirstStage", MIN(ProcessData[seq]),
"LastStage", MAX(ProcessData[seq]) 
),
[FirstStage] = 10 && [LastStage] = 100 
)
)

If this post helps, then I would appreciate a thumbs up 👍 and mark it as the solution to help the other members find it more quickly.