Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.