Trying to determine number of clients with inactive cases

[evan_hoefling]

Hey all, I am new to power bi and am trying to determine which of my clients do not have an active cases. My data is structured as follows: (EXAMPLE)

case table

case_id	active_case
1	Yes
2	No
3	Yes

client table (this is where I am trying to create the column)

client_id	active_cases
1	???
2	???
3	???

cases_to_client

case_id	client_id
1	1
1	3
2	2
3	1
3	3

ideally the client table would look like this in the end:

client_id	only_active_cases
1	Yes
2	No
3	Yes

I am trying to display a count for only active cases.

[jeroendekk · Accepted solution]

Hi @evan_hoefling 

First i would merge the case table and the cases_to_client table to get the active state on the cases_to_client table. This makes it simpler.

You could then add a column with this function.

Only active cases = 
VAR CountofNo =
    COUNTROWS (
        FILTER (
            RELATEDTABLE ( cases_to_client ),
            cases_to_client[ active_case] = "No"
        )
    )
RETURN
    IF ( CountofNo > 0, "No", "Yes" )

 
However if you just want a count of inactive / or active cases, it is better to not do a calculated column and solve it with measures.

Count of active = 
CALCULATE (
    COUNTROWS ( cases_to_client ),
    cases_to_client[ active_case] = "Yes"
) + 0


Count of inactive = 
CALCULATE (
    COUNTROWS ( cases_to_client ),
    cases_to_client[ active_case] = "No"
) + 0

 
I hope this helps
If my awnser helped you please not this as resolved and kudos are welcome.

Jeroen Dekker