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.
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.
Solved! Go to 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
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
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 |
---|---|
51 | |
27 | |
24 | |
13 | |
8 |
User | Count |
---|---|
74 | |
56 | |
47 | |
16 | |
12 |