Evaluating a boolean properly in a measure

[Anonymous]

I'm not sure if this is a context issue, or not. I have a "simple" measure like this:

Deviation Calc = 
VAR CurrentVal = [% of Bnch Growth by Cat] 
VAR PercRemainder = [Avg Cat Deviation Value] 
VAR Diff = ABS(PercRemainder - CurrentVal)
VAR IsDependent = [CategoryIsDependent]
VAR __calc = IF( Diff > [Allowed Progam % Deviation], [Allowed Progam % Deviation], Diff)
VAR Result = IF([CategoryIsDependent], __calc, BLANK())
RETURN
    RESULT

[IsDependent] properly returns True or False in a table visual based on the row's value (and yes I thought through the logic, and it has to converted to a measure because of context).

The goal is if the boolean is false return a blank for that particular row, or return the calculated value (which works fine). The reason is that this measure is to be used to drive conditional formatting, but you can't apply two levels of conditional formatting, so I need a way to exclude certain rows that should not be formatted with the conditional formatting. I'm using a color scale, so I need to keep the values.

[technolog · Accepted solution]

The main logic you're trying to implement is to return a value based on the CategoryIsDependent measure. If it's True, you want to return the calculated value (__calc), and if it's False, you want to return a blank.

From your description, it seems that the [CategoryIsDependent] measure is working correctly in a table visual, returning True or False as expected. So, the main logic of your measure is in the Result variable, which checks the value of [CategoryIsDependent] and returns either __calc or BLANK() based on its value.

Your DAX measure seems correct for the requirement you've mentioned. If [CategoryIsDependent] is True, it will return the value of __calc, and if it's False, it will return BLANK(). This should effectively exclude rows with a False value from being formatted in the conditional formatting, as rows with a blank value won't be considered in the color scale.

If you're facing any issues with this measure, it might be related to the context in which it's being evaluated. Remember, DAX is highly context-dependent, and the results can vary based on the filters and context applied to the data. Ensure that the other measures like [Allowed Progam % Deviation], [Avg Cat Deviation Value], and [% of Bnch Growth by Cat] are working as expected and returning the correct values in the context where you're evaluating the Deviation Calc measure.