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

Hi,

I have just added the following calculated table using the DAX code below...

 

ProjectTable = 
DATATABLE(
    "Value.Name",STRING,
    "Risk.Score",STRING,
    "Count",INTEGER,
    {
        {"32670-Teller Capture","B","3"},
        {"32670-Teller Capture","C","2"},
        {"32670-Teller Capture","D","3"},
        {"32841-Universal OB","A","1"},
        {"32841-Universal OB","B","2"},
        {"32841-Universal OB","C","2"},
        {"32841-Universal OB","D","3"}
    }
)

 

Then tried to calculate your "Complex Rating" calculated column.

 

Complex Rating = 
SWITCH(
    TRUE(),
    ProjectTable[Risk.Score]="A" && ProjectTable[Count]>=2,"Complex",
    ProjectTable[Risk.Score]="B" && ProjectTable[Count]>=2,"High",
    ProjectTable[Risk.Score]="C" && ProjectTable[Count]>=3,"Medium",
    "Low")

 

This is giving the results. Can you try it out?