Forum Discussion
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 by delimiter and group the values first by risk score, then by project, giving me a count of each risk score as shown here:
What I'm trying to do is create something like an IF(AND) statement you would use in Excel, to say if a project has a risk score of "A" and the count of A's is >2, show a complex rating. This is the list of values I'd like to see:
>2 A's = Complex
>2 B's = High
>3 C's = Medium
All Other = Low
So far I haven't been able to find a calculation searching articles that will allow me to do this. I would love some help here.
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,
5 Replies
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?
Hi Anonymous ,
We can try to create a calculated column to meet your requirement:
Column = VAR valuename = [Value.name] VAR Complex = CALCULATE ( COUNTROWS ( 'Table' ), 'Table', 'Table'[Value.name] = valuename, 'Table'[Count] > 2, 'Table'[Risk Score] = "A" ) > 0 VAR High = CALCULATE ( COUNTROWS ( 'Table' ), 'Table', 'Table'[Value.name] = valuename, 'Table'[Count] > 2, 'Table'[Risk Score] = "B" ) > 0 VAR Medium = CALCULATE ( COUNTROWS ( 'Table' ), 'Table', 'Table'[Value.name] = valuename, 'Table'[Count] > 3, 'Table'[Risk Score] = "C" ) > 0 RETURN SWITCH ( TRUE (), Complex, "Complex", High, "High", Medium, "Medium", "Low" )If it doesn't meet your requirement, Could you please show the exact expected result based on the Tables that you have shared？
By the way, PBIX file as attached.
Best regards,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" )
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,