Calculated column returning all results

2
votes

I have a colleague table (named 'colleagues') which lists all of the colleagues in the department. I have another table (named 'cases') which lists all of the cases worked by all of the colleagues. In the cases table, there is a column called 'outcome' which will be either Good, Satisfactory or Bad depending on how well the case was dealt with.

colleagues table:

enter image description here

cases table:

enter image description here

There is a one to many relationship between the colleague table and the cases table. I am trying to create a calculated column in the colleague table which will sum how many 'Good' outcome cases they had in total. This the calculated column formula I have:

CALCULATE(COUNTROWS('cases'), FILTER('cases', 'cases'[outcome]="Good")

This calculated column is just adding all of the 'Good' cases for everyone rather than just the individual colleague. See calculated column below with column name 'Good':

enter image description here

The expected behavior is that the column would calculate the number of 'Good' cases each colleague had. This is the expected outcome:

enter image description here

2
powerbidax

2 Answers

2
votes

Create a calculated column as follows:

CountValues =
             CALCULATE(COUNTROWS('cases'),'cases'[outcome]="good")

p.s. If this (or another) answer helps you, please take a moment to "accept" the answer that helped by clicking on the check mark beside the answer to toggle it from "greyed out" to "filled in".

2
votes

Another solution that doesn't involve CALCULATE is

COUNTROWS( FILTER( RELATEDTABLE('cases'), 'cases'[outcome]="Good" ) )