DAX for Multiple If, and, or statements

[kristi_in_heels]

I am trying to create a measure that I can then apply as a rule for conditional formatting.

I would like to achieve the following:

Highlight to be applied if:

• [Baseline Value] is below $100,000, and [Variance%] is +/- 100%; OR
• [Baseline Value] between $100,001 to $300,000, and [Variance%] is +/- 50%; OR
• [Baseline Value] between $300,001 top $500,000, and [Variance%] is +/-25%; OR
• [Baseline Value] over $500,000, and CPS vs [Variance%] is +/- 20%

Any assistance is appreciated.

Thank you in advance.

[aduguid · Accepted solution]

Try this measure

Conditional Formatting Measure = 
SWITCH(
    TRUE(),
    [Baseline Value] < 100000 && ABS([Variance%]) >= 100, 1,
    [Baseline Value] >= 100001 && [Baseline Value] <= 300000 && ABS([Variance%]) >= 50, 1,
    [Baseline Value] >= 300001 && [Baseline Value] <= 500000 && ABS([Variance%]) >= 25, 1,
    [Baseline Value] > 500000 && ABS([Variance%]) >= 20, 1,
    0
)

[kristi_in_heels]

Thank you both, both of these measures work, with one exception.

Instead of 100 / 50 / 25 / 20, I changed the measure to 1 / 00.5 / 0.25 / 0.2 and both work perfectly.

Appreciate you both taking the time to respond.

[bhanu_gautam · Accepted solution]

 , Create a measure for this 

ConditionalFormattingMeasure =

   VAR BaselineValue = [Baseline Value]

   VAR VariancePercent = [Variance%]

   RETURN

       SWITCH(

           TRUE(),

           BaselineValue < 100000 && ABS(VariancePercent) >= 100, 1,

           BaselineValue >= 100001 && BaselineValue <= 300000 && ABS(VariancePercent) >= 50, 1,

           BaselineValue >= 300001 && BaselineValue <= 500000 && ABS(VariancePercent) >= 25, 1,

           BaselineValue > 500000 && ABS(VariancePercent) >= 20, 1,

           0

       )

 
This measure will return 1 if any of the conditions are met and 0 otherwise 

Once you have created the measure, you can use it to apply conditional formatting to your table or matrix visual.

Select the table or matrix visual where you want to apply the conditional formatting.
Go to the Format pane.
Expand the Conditional formatting section.
Choose the field you want to format (e.g., [Baseline Value] or [Variance%]).
Click on Advanced controls.
In the Conditional formatting dialog, select Format by as Field value.
In the Based on field dropdown, select the measure you created (ConditionalFormattingMeasure).
Set the formatting options (e.g., font color, background color) based on the value of the measure.
Set Formatting Rules:

If the measure returns 1, apply the desired formatting (e.g., highlight the cell).
If the measure returns 0, no formatting is applied.

@kristi_in_heels

[aduguid · Accepted solution]

Try this measure

Conditional Formatting Measure = 
SWITCH(
    TRUE(),
    [Baseline Value] < 100000 && ABS([Variance%]) >= 100, 1,
    [Baseline Value] >= 100001 && [Baseline Value] <= 300000 && ABS([Variance%]) >= 50, 1,
    [Baseline Value] >= 300001 && [Baseline Value] <= 500000 && ABS([Variance%]) >= 25, 1,
    [Baseline Value] > 500000 && ABS([Variance%]) >= 20, 1,
    0
)