Grouping/summarizing one measure by another measure - with possibility of filter

[nhoff]

I have two tables:

- Cases (CaseID, ClientID, RegionID)

- Tasks (TaskID, refCaseID)

1 case can have many tasks. Tables are linked by CaseID

I would like to create an overview of how many cases have 1, 2, 3....n tasks and I would like to be able to filter results by columns from Cases, e.g. RegionID. Example data:

CountCases	CountTasks
512	0
239	1
112	2
37	3
<blank>	4
12	5

Example: 112 cases has exactly 2 associated tasks.

I have created this table, but I does not respond to filters/slicers fro table Cases, e.g. filtering the result table by RegionID

CasesWithTask =

var tbltemp = GENERATESERIES(0;20)

return

CALCULATETABLE(

SUMMARIZE(tbltemp;

[Value]; --default column name from GenerateSeries

"CountCases";

CALCULATE(

[CountCases];   --measure counting rows from table Cases

FILTER('Cases';[Count Tasks] = [Value])  --measure counting rows from table Tasks

)

)

)

Any suggestions?

[PaulDBrown · Accepted solution]

@nhoff 

See if this works for you. For this exercise, I'm using these tables:

And have the model set up like this:

now calculate the number of tasks (DISTINCTCOUNT in this example) for each Case ID:

Count of tasks = DISTINCTCOUNT(TaskTable[Task]) +0 

Next generate a new table using the "New Table" option under "Modeling" in the ribbon using the DAX:

Summarized CaseID tasks = SUMMARIZE(CaseIDTable; CaseIDTable[CaseID]; 
                            "Numb of tasks" ; [Count of tasks])

Go to the modeling pane and set up a relationship between your CaseIDTable and the new table you've just created to look like this:

Next create a measure to count the rows in the new table:

Count Case IDs by Numb of tasks = COUNTROWS('Summarized CaseID tasks')

And now  you can create a table visual using the "Numb of tasks" column from the new table and the last measure you've created:

add your slicers from the CaseIdTable and you get this:

[nhoff]

@PaulDBrown 

Thanks a lot! Worked perfectly.

I would have preferred to not have (yet) another physical table as my model already is far too crowded, even though my own suggestion included this.

I appreciate the effort and meticulousness you put into it. Great.

Rgds

Nick