Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Calculated Column Based on 2 conditions

Hello, I'm needing to give projects a risk rating based on whether conditions in two columns are met. I start with a column showing risk scores like this: I've been able to split the column b...
  • v-lid-msft's avatar
    v-lid-msft
    6 years ago

    Hi Anonymous ,

     

    Does there any possbile that 'Risk Rating'[Risk_Score] column contain some space char? Please try to create a new calculated column TestColumn = LEN( 'Risk Rating'[Risk_Score]) to debug with it.

     

    Or we can try to use some other functions in the formula:

     

    Risk_Rating =
VAR valuename = [Value.name]
VAR Complex =
    CALCULATE (
        COUNTROWS ( 'Risk Rating' ),
        FILTER (
            ALL ( 'Risk Rating' ),
            'Risk Rating'[Value.name] = valuename
                && 'Risk Rating'[Count] > 2
                && CONTAINSSTRING ( 'Risk Rating'[Risk_Score], "A" )
        )
    ) > 0
VAR High =
    CALCULATE (
        COUNTROWS ( 'Risk Rating' ),
        FILTER (
            ALL ( 'Risk Rating' ),
            'Risk Rating'[Value.name] = valuename
                && 'Risk Rating'[Count] > 2
                && CONTAINSSTRING ( 'Risk Rating'[Risk_Score], "B" )
        )
    ) > 0
VAR Medium =
    CALCULATE (
        COUNTROWS ( 'Risk Rating' ),
        FILTER (
            ALL ( 'Risk Rating' ),
            'Risk Rating'[Value.name] = valuename
                && 'Risk Rating'[Count] > 3
                && CONTAINSSTRING ( 'Risk Rating'[Risk_Score], "C" )
        )
    ) > 0
RETURN
    SWITCH ( TRUE (), Complex, "Complex", High, "High", Medium, "Medium", "Low" )


    Best regards,

     

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Thanks v-lid-msft. This seems like it could work. I pasted it in and changed to my table names, but for some reason could only get a rating of "Low" to show up. I will keep seeing what I can change with this. Here's what I entered as my calculated column:

 

Risk_Rating =

VAR valuename = [Value.name]

VAR Complex =

CALCULATE (

COUNTROWS ( 'Risk Rating' ),

'Risk Rating',

'Risk Rating'[Value.name] = valuename,

'Risk Rating'[Count] > 2,

'Risk Rating'[Risk_Score] = "A"

) > 0

VAR High =

CALCULATE (

COUNTROWS ( 'Risk Rating' ),

'Risk Rating',

'Risk Rating'[Value.name] = valuename,

'Risk Rating'[Count] > 2,

'Risk Rating'[Risk_Score] = "B"

) > 0

VAR Medium =

CALCULATE (

COUNTROWS ( 'Risk Rating' ),

'Risk Rating',

'Risk Rating'[Value.name] = valuename,

'Risk Rating'[Count] > 3,

'Risk Rating'[Risk_Score] = "C"

) > 0

RETURN

SWITCH ( TRUE (), Complex, "Complex", High, "High", Medium, "Medium", "Low" )

v-lid-msft's avatar
v-lid-msft
Community Support
6 years ago

Hi Anonymous ,

 

Does there any possbile that 'Risk Rating'[Risk_Score] column contain some space char? Please try to create a new calculated column TestColumn = LEN( 'Risk Rating'[Risk_Score]) to debug with it.

 

Or we can try to use some other functions in the formula:

 

Risk_Rating =
VAR valuename = [Value.name]
VAR Complex =
    CALCULATE (
        COUNTROWS ( 'Risk Rating' ),
        FILTER (
            ALL ( 'Risk Rating' ),
            'Risk Rating'[Value.name] = valuename
                && 'Risk Rating'[Count] > 2
                && CONTAINSSTRING ( 'Risk Rating'[Risk_Score], "A" )
        )
    ) > 0
VAR High =
    CALCULATE (
        COUNTROWS ( 'Risk Rating' ),
        FILTER (
            ALL ( 'Risk Rating' ),
            'Risk Rating'[Value.name] = valuename
                && 'Risk Rating'[Count] > 2
                && CONTAINSSTRING ( 'Risk Rating'[Risk_Score], "B" )
        )
    ) > 0
VAR Medium =
    CALCULATE (
        COUNTROWS ( 'Risk Rating' ),
        FILTER (
            ALL ( 'Risk Rating' ),
            'Risk Rating'[Value.name] = valuename
                && 'Risk Rating'[Count] > 3
                && CONTAINSSTRING ( 'Risk Rating'[Risk_Score], "C" )
        )
    ) > 0
RETURN
    SWITCH ( TRUE (), Complex, "Complex", High, "High", Medium, "Medium", "Low" )


Best regards,