In my output (OLAP) table I want to have each activity name from the activity table and then an overview of the number of cases in which that activity name has occurred. Then I want to get an overview of the number of cases in which that activity name has occurred two times, three times, or multiple times etc.

I know that with the following code I can get the number of cases where the activity happened at least 1 time, but I do not know how to calculate the number of cases where the activity happened 1 time and not more often.

SUM(

CASE WHEN

 INDEX_ACTIVITY_TYPE ("ACTIVITY_TABLE"."ACTIVITY") = 1

 THEN 1

 ELSE 0

 END

)

The other thing I still miss is how I can calculate the number of cases in which that activity occurred >=2 times. When I change the formula to >=2, I get the wrong count as then a case with 4 activity occurrences is counted as 3 instead of 1 (it counts the second occurrence, third occurrence and fourth occurrence all as one 'case'). Is there anyway to count only the distinct cases?