cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
sqlwiz
New Member

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

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.

1 ACCEPTED SOLUTION
v-yuta-msft
Community Support
Community Support

Hi sqlwiz,

 

First create a calculate column:

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

1.PNG  

Then create a measure using DAX below:

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

2.PNG 

 

Regards,

Jimmy Tao

View solution in original post

1 REPLY 1
v-yuta-msft
Community Support
Community Support

Hi sqlwiz,

 

First create a calculate column:

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

1.PNG  

Then create a measure using DAX below:

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

2.PNG 

 

Regards,

Jimmy Tao

Helpful resources

Announcements
PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

MPPC 2023 PBI Carousel

Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.

Top Solution Authors