cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
npals Frequent Visitor
Frequent Visitor

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 if this instance does not exist within that row, the value would equal "no" or "false".  There are a total of 23 columns.  Other values would possibly exist within this row , like "N/A" for example, but these would not matter when it comes to the result of no before yes.  

 

Note:  The no would not need to exist directly before the yes.  For example, the row could read "No" "No" "Yes" "N/A" "No" "Yes"

Simply because a no reads before a yes within the row, the value would have to be "1" or "true".

 

true.png

Please help by providing a calculated column or a measure, thank you in advance.

 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Detect No before Yes

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

 

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




1 REPLY 1
Super User
Super User

Re: Detect No before Yes

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

 

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries