Count how many sub-activities were created based on an activity

[sqlwiz]

I have a dimension that stores workflows(cases, subcases). I would like to do a count of how many subcases are created for each case.

Workflow Dimension

Workflow
------------------------------
Case Number    WorkflowType
------------------------------ 
10             Case
20             Case
30             Case
20-1           Subcase
20-2           Subcase
20-3           Subcase
10-1           Subcase

The desire output I would like is, for every case count how many subcases were created.

Workflow
------------------------------------------------
Case Number    WorkflowType      CountOfSubcases
------------------------------------------------
10             Case               1
20             Case               3
30             Case               0
------------------------------------------------
Total                             4

I have a current dax measure that works, but the total at the bottom does not show when looking at multiple rows, only display when one case is selected.

Total Subcases = 
VAR CC = FIRSTNONBLANK ( Workflow[Case Number], 1 )
RETURN
COUNTX (
    FILTER (
        ALL( Workflow ),
        SUBSTITUTE ( Workflow[Case Number], RIGHT ( Workflow[Case Number], 2 
          ), "" )
            = CC
            && Workflow[WorkflowType] = "SubCase"
    ),
    Workflow[WorkflowID]
 )

If anybody could help me tweak my measure or present with a new measure, that would be great.

Thanks in advance.

[v-yuta-msft · Accepted solution]

Hi sqlwiz,

First create a calculate column:

New_Column = 
SUBSTITUTE ( Workflow[Case Number], RIGHT ( Workflow[Case Number], 2 ), "" )

  

Then create a measure using DAX below:

Total Subcases = 
CALCULATE (
    COUNT ( Workflow[Case Number] ),
    FILTER (
        ALLEXCEPT ( Workflow, Workflow[New_Column] ),
        Workflow[WorkflowType] = "SubCase"
    )
)

 

Regards,

Jimmy Tao