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 with a 'Complete' or start with 'New Email'  needs to be ignored. 

 

IDProcess StatusSeq
11ANEW EMAIL10
11APROCESSING20
11APROCESSING TRANSFORMATION30
11APROCESSING TRANSFORMATION30
11APROCESSING TRANSFORMATION30
11APROCESSING OUTPUT40
11AVALIDATING50
11AWORKQUEUE70
11ACOMPLETE100
12ANEW EMAIL10
12APROCESSING20
12APROCESSING TRANSFORMATION30
12APROCESSING TRANSFORMATION30
12APROCESSING TRANSFORMATION30
12APROCESSING OUTPUT40
12AVALIDATING50
12AWORKQUEUE70
12ACOMPLETE100
13BNEW EMAIL10
13BPROCESSING 20
13BTERMINATING1000
14CNOTIFICATION99
15DNEW EMAIL10
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!

 

 

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.

     

     

2 Replies

  • 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. 

  • Anonymous's avatar
    Anonymous
    Not applicable
    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.