Help with an IF for blank columns

[MCacc]

Hello, 

I'm trying to build a measure wich can show dynamically, depending on whether a specific column is populated or not, the theshold for my measure. Once I achieved this, I would like to use this measure to set a conditional formatting for the backgroynd color of my tablix visual. 

This is my model:

BANK     |    INDICATOR >  |  INDICATOR <     

BANK_1  |    12                   |
BANK_1  |    12                   |
BANK_2  |    4                     |
BANK_2  |    4                     |
BANK_3  |    67                   |
BANK_3  |    67                   |
BANK_4  |    5                     |
BANK_4 | 5 |

So, the scenario is this: I have 2 coulumns wich, through a portal, my client can insert an input value, then my PowerBI will read this value as normal as any other value from a source. More than one column cannot be populated, therefore, only one column can be populated at the time. 

Having said so, depending on which column my client decides to populate, my measure should show the value and create a theshold for my measure.

Then, in my conditional formatting I will have a specific background color for my tablix cell depending on the value my customer has written. 

In this example, if my measure shows 11 for BANK_1, and my value in the column INDICATOR > is 12, and I want my measure to be > than my indicator.

So because, my measure is 11 for BANK_1 and my column value INDICATOR > is 12, the background color will be red

Now, let's say that, now my client wants to populate the other column (INDICATOR <), and I have the same value for both my measure and column.Therefore, if my measure shows 11 for BANK_1,and my value in the column INDICATOR < is 12, and I want my measure to be > than my indicator.

So because, my measure is 11 for BANK_1 and my column value INDICATOR < is 12, the background color will be green.

I have written this measure down:

IF(NOT(ISBLANK(MAX(TABLE[INDICATOR > ]))),
   IF ([MY MEASURE] > MAX(TABLE[INDICATOR > ]) , 1,
      IF (NOT(ISBLANK(MAX(TABLE[INDICATOR <]))),
           IF ([MY MEASURE] < MAX(TABLE[INDICATOR > ]) , 1,0 )
                            )
                         )
                    )
                 )

So, because 1 is when I know my column is populated, I simply go to conditional formatting, set this the rule on field value, if My_Measure_for_Theshold = 1 ---> COLOR RED

It works perfectly when the first column is populated:

IF(NOT(ISBLANK(MAX(TABLE[INDICATOR > ]))),
   IF ([MY MEASURE] > MAX(TABLE[INDICATOR > ]) , 1,

Now, the issue is that it seems to be not entering the second if (the case when the second column is populated and not the first).

IF (NOT(ISBLANK(MAX(TABLE[INDICATOR <]))),
           IF ([MY MEASURE] < MAX(TABLE[INDICATOR > ]) , 1, ...

Any suggestion?

Please do not hesitate to ask me any further question. I understand the scenario is a bit complicated. 

Thank you so much for your help

[v-kkf-msft · Accepted solution]

Hi @MCacc ,

In your code, the conditional judgment for the second column is only entered when [MY MEASURE] <= MAX(TABLE[INDICATOR > ]).

So you're setting up conditional formatting for these two columns? Then please use two measures.

Measure1 = 
IF(NOT(ISBLANK(MAX(TABLE[INDICATOR > ]))),
   IF ([MY MEASURE] > MAX(TABLE[INDICATOR > ]) , 1))

Measure2 =
IF (NOT(ISBLANK(MAX(TABLE[INDICATOR <]))),
   IF ([MY MEASURE] < MAX(TABLE[INDICATOR > ]) , 1,0 ))

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.