Forum Discussion

npals's avatar
npals
Frequent Visitor
8 years ago
Solved

Detect No before Yes

Hello,   I am trying to create a new measure or calculated column that will detect if "no" exists before "yes" within a row.  If this instance does exist, the value would equal "1" or "true", and i...
  • ImkeF's avatar
    ImkeF
    8 years ago

    In the query editor, you add a column with this formula:

     

    List.PositionOf(Record.FieldValues(_), "No") < List.PositionOf(Record.FieldValues(_), "Yes")

    if you need a case-insensitve check, you transfer to lower like this:

     

    List.PositionOf(Record.FieldValues(Text.Lower(_)), "no") < List.PositionOf(Record.FieldValues(Text.Lower(_)), "yes")

     

ImkeF's avatar
ImkeF
Community Champion
8 years ago

In the query editor, you add a column with this formula:

 

List.PositionOf(Record.FieldValues(_), "No") < List.PositionOf(Record.FieldValues(_), "Yes")

if you need a case-insensitve check, you transfer to lower like this:

 

List.PositionOf(Record.FieldValues(Text.Lower(_)), "no") < List.PositionOf(Record.FieldValues(Text.Lower(_)), "yes")