Multiple Column Criteria for Nested IF

[mshodge]

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

[parry2k · Accepted solution]

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

[ImkeF]

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. 

[mshodge]

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")