Forum Discussion
Create custom column based on values in multiple other columns
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!
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 )
6 Replies
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.
I tried the parentheses but that didn't work.
Yes, I figured and that is what I tried but it didn't work.
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 )
