Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
81 | |
66 | |
58 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |