Solved: Re: Multiple Column Criteria for Nested IF

mshodge · ‎01-10-2017

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 · ‎01-10-2017

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

Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤

Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

View solution in original post

parry2k · ‎01-10-2017

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

Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤

Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

ImkeF · ‎01-10-2017

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.

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

mshodge · ‎01-10-2017

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