Create custom column based on values in multiple other columns

[cathoms]

Hello!

I have columns for values that compare how a provider is utilizing office visit codes versus their peer group. I need to flag values at certain thresholds so I created custom columns to flag so-called "opportunities", which I then use for conditional formatting. One of the flags depends on the values in the other columns and I can't get it to work.

Here is what the current end product looks like:

The problem is that 99215 Diff should flag when below -10 but only if there are no other flags. As you can see, that is not happening. What am I doing wrong?

Here is the M Code:

= Table.AddColumn(#"Added 99214 Opportunity", "99215 Opportunity", 
each if [99215 Diff] < -0.1 and [99212 Opportunity] = 1 or [99213 Opportunity] = 1 or [99214 Opportunity] = 1 then "0"
else if [99215 Diff] < -0.1 then "1"
else "0")

I would appreciate any help!

[cathoms · Accepted solution]

I decided it would be easier to just create a new column using DAX, which is probably what I should have done from the start. Anywho, my DAX code for the column looks like this:

99215 Potential = 
IF(
    EstVisits_21Q1[99215 Diff] < -0.1 && NOT(
        EstVisits_21Q1[99212 Opportunity] = 1 || EstVisits_21Q1[99213 Opportunity] = 1 || EstVisits_21Q1[99214 Opportunity] = 1
    ), 1,
    0
)

[watkinnc]

Sorry, the second parentheses should go after the [99214 Opportunity] =1) like that. 

--Nate

[cathoms]

Yes, I figured and that is what I tried but it didn't work.

[watkinnc]

Try this:

Just place parentheses before [99212 Opportunity] and then after [99214 Opportunity] so that all of the "or"s after the "and" are a single parenthesized statement.

--Nate

[cathoms]

I tried the parentheses but that didn't work.

[cathoms]

Note that I did try changing the order and putting the "else if" statement first to no avail. I also tried adding parentheses around the set of "or" statements to no effect.