Count occurrence of string value in a column

[CF123]

Hi, I am creating a risk dashboard, and I want to display some stats about the risks. I have a table of risks and their attributes, such as their risk rating. I want to create a seperate table that counts how many risks have each rating. If there are no risks this with a particular rating, I want the column to return 0.

Here's an example of the expected behaviour

 Table 1

Risk ID	Risk Rating
R-1	Moderate
R-2	Severe
R-3	Severe
R-4	Critical
R-5	Moderate
R-6	Moderate

Table 2

Risk Rating	Count
Sustainable	0
Moderate	3
Severe	2
Critical	1

[Ritaf1983]

HI @CF123 ,
Due to my misunderstanding of what you would like to do, I am offering two alternatives
If you are seeking a summarized table as a "data table," you can use the Group by clause in PQ to group by a count.
For the purpose of visualization, it is a count using DAX's COUNT function.
The table will know how to filter it and show the correct count depending on each status once you present it in the table.
This answer offers a similar solution:
https://community.powerbi.com/t5/Desktop/Counting-Buckets/m-p/3136402#M1060789
Please attach a PBIX file with an example if it's something else so we can help you more easily.
If I answered your question, please mark my post as solution, Appreciate your Kudos

[CF123]

I want to have a calculated column in table 2 that counts the occurenance of each risk rating in table 1. If a particular rating can't be found in table 1, I need the calculated column to return 0

[Ritaf1983 · Accepted solution]

Okay,
This can be achieved by relating the tables and using the relatedtable function.
https://learn.microsoft.com/en-us/dax/relatedtable-function-dax
I can show the solution if you share the PBIX file with an example.

If I answered your question, please mark my post as solution, Appreciate your Kudos

[CF123]

Thanks, I was able to figure it out 🙂

[Ritaf1983]

Great job 😍!
Please mark my answer as a solution if it is the result of my idea and guidance. This will help others who encounter the same issue.