Forum Discussion

RRaj_293's avatar
RRaj_293
Helper III
1 year ago
Solved

Count distinct field based on process Sequence

  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 wit...
General Comment
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    1 year ago

    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's avatar
Selva-Salimi
Solution Sage
1 year ago

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.