Forum Discussion
Multiple Column Criteria for Nested IF
Folks, there must be a better way than this IF statement which works but is just ugly. My rows represent an operator maintenance inspection which includes 16 columns each which represents a specific maintenance check. The inputs that come in those columns are either CHECKED or *ISSUE*. All I need for BI to do is create a column that states whether ANY of the of the 16 cells in the row say *ISSUE* at which time the calculated column would say *ISSUE* else CHECKED.
SWITCH doesn't support multiple columns. FILTER doesn't support more than two columns. Couldn't get FIND or ALL to work either.
HasIssue = IF( 'Form Responses 1'[Horn Alarm]="*ISSUE*"||'Form Responses 1'[Lights]="*ISSUE*","ISSUE",IF('Form Responses 1'[Tires]="*ISSUE*"||'Form Responses 1'[Controls]="*ISSUE*","ISSUE",IF('Form Responses 1'[Brakes]="*ISSUE*"||'Form Responses 1'[Lift]="*ISSUE*","ISSUE",IF('Form Responses 1'[Steering]="*ISSUE*"||'Form Responses 1'[Leaks]="*ISSUE*","ISSUE",IF('Form Responses 1'[Smell]="*ISSUE*"||'Form Responses 1'[Overhead]="*ISSUE*","ISSUE",IF('Form Responses 1'[Gauges]="*ISSUE*"||'Form Responses 1'[Seat Belt]="*ISSUE*","ISSUE",IF('Form Responses 1'[Propane Tk]="*ISSUE*"||'Form Responses 1'[Markings]="*ISSUE*","ISSUE",IF('Form Responses 1'[Lanyard]="*ISSUE*"||'Form Responses 1'[Harness]="*ISSUE*","CHECKED"))))))))
Any advice? Just a point in the right direction. No need to reword anything.
P.S. yes i could do this in Google Sheets prior to the Query but I've failed at their dynamic named range calculations
Matt
not sure but i think it will work:
myvalue = if(col1 = "Issue" || col2 = "Issue" || col3 = "Issue" || col4 = "Issue", "Issue", "Not Issue")
Basicaly you can have multiple fields in OR
3 Replies
not sure but i think it will work:
myvalue = if(col1 = "Issue" || col2 = "Issue" || col3 = "Issue" || col4 = "Issue", "Issue", "Not Issue")
Basicaly you can have multiple fields in OR
You can add a column in the query-editor with this formula:
if List.Contains(Record.FieldValues(_), "*ISSUE*") = true then "*ISSUE*" else "CHECKED"
Record.FieldValues(_) will transform the current row into a list and
List.Contains will check if your keyword is contained.
Yes!!
That does simplify.
HasIssue = IF( 'Form Responses 1'[Horn Alarm]="*ISSUE*"||'Form Responses 1'[Lights]||'Form Responses 1'[Tires]="*ISSUE*"||'Form Responses 1'[Controls]||'Form Responses 1'[Brakes]="*ISSUE*"||'Form Responses 1'[Lift]="*ISSUE*"||'Form Responses 1'[Steering]="*ISSUE*"||'Form Responses 1'[Leaks]="*ISSUE*"||'Form Responses 1'[Smell]="*ISSUE*"||'Form Responses 1'[Overhead]="*ISSUE*"||'Form Responses 1'[Gauges]="*ISSUE*"||'Form Responses 1'[Seat Belt]="*ISSUE*"||'Form Responses 1'[Propane Tk]="*ISSUE*"||'Form Responses 1'[Markings]="*ISSUE*"||'Form Responses 1'[Lanyard]="*ISSUE*"||'Form Responses 1'[Harness]="*ISSUE*","ISSUE","CHECKED")
