Measure two columns

[SKEUK]

Hello all,

Apologies, I am incredibly new to Power BI and am working on a very basic model and I am currently stuck.

I have a risk register spreadsheet with a description of the risk, the status which is either 'open' or 'closed' and a Rating which is either 'catastrophic', 'high', 'medium' or 'low'.

I want to create a measure 'High Risk' which will capture open risks with a rating of catastrophic or high and then 'Low Risk' which will capture open risks with a rating of medium or low.

I cannot for the life of me figure out the correct formula for this and I imagine it is very simple.

Is anyone able to advise please?

[SKEUK]

So would it be like this?

High rating = CALCULATE(

COUNT('Risk Register 2025'[Status])

,'Risk Register 2025' IN {"Catastrophic", "High"}

)

I get the message 'The syntax for High is incorrect

[SamWiseOwl · Accepted solution]

Almost it looks like you are missing a column name in filter bit and to only show the open items:

High rating = CALCULATE(

COUNT('Risk Register 2025'[Status])

,'Risk Register 2025'[RatingColumn] IN {"Catastrophic", "High"}

,'Risk Register 2025'[Status] = "Open"

)

[SamWiseOwl]

Hi @SKEUK 

You can create a measure using the Calculate function. We can add a filter onto the column and using IN list out the items we want to return.

High rating =
Calculate(
              Count(table[RatingColumn])
             , table[Ratingcolumn] IN {"Catstrophic", "High"}
             ,table[Statuscolumn] = "Open"
)

Low rating =
Calculate(   Count(table[RatingColumn])
             , table[Ratingcolumn] IN {"Medium", "Low"}
             ,table[Statuscolumn] = "Open")

[SKEUK]

Thank you!