DAX Switch function not working

[petergarry111]

Hi,

I am having issues getting my DAX conditional formatting function to work, however it shows no errors when I've created it but won't let me select it in the "Field Value" of my conditional formatting section. Is there any experts out there that could help?

This is the function:

_Format BG Compliance =
SWITCH(TRUE(),
IF(CALCULATE(DIVIDE(SUM('Longitudinal Data'[Compliant]),SUM('Longitudinal Data'[Target Audience])),'Control Table'[Metric]="Appraisals")>=0.895,"C6EFCE","FFC7CE"),
IF(CALCULATE(DIVIDE(SUM('Longitudinal Data'[Compliant]),SUM('Longitudinal Data'[Target Audience])),'Control Table'[Metric]="BTP: Blood Components")>=0.895,"C6EFCE","FFC7CE"),
IF(CALCULATE(DIVIDE(SUM('Longitudinal Data'[Compliant]),SUM('Longitudinal Data'[Target Audience])),'Control Table'[Metric]="Conflict Resolution")>=0.895,"C6EFCE","FFC7CE"),
IF(CALCULATE(DIVIDE(SUM('Longitudinal Data'[Compliant]),SUM('Longitudinal Data'[Target Audience])),'Control Table'[Metric]="Fire Safety")>=0.895,"C6EFCE","FFC7CE"),
"#FFFFFF"
)

Many Thanks,

Pete.

[petergarry111]

Thanks for the help, all working well now. 🙂 

[Anonymous · Accepted solution]

Hi @petergarry111 ,

Thanks for the reply from FarhanJeelani .

There is an error in the nesting of SWITCH and IF in your MEASURE. Here is a screenshot of the parameter parsing for the SWITCH function.

Applying it to your measure, you can see that the red box is the value and the blue box is the result, which means that the value returned when you value is true is the value of the result, not the color value in the nested IF function.

FarhanJeelani's answer is correct, applying his measure as a conditional format works fine, in my example his measure is named Measure1.

I have another way to accomplish your needs, create a measure named _Format BG Compliance:

_Format BG Compliance = 
SWITCH (
    TRUE (),
    CALCULATE (
        DIVIDE (
            SUM ( 'Longitudinal Data'[Compliant] ),
            SUM ( 'Longitudinal Data'[Target Audience] )
        ),
        'Control Table'[Metric] = "Appraisals"
    ) >= 0.895, "#C6EFCE",
    CALCULATE (
        DIVIDE (
            SUM ( 'Longitudinal Data'[Compliant] ),
            SUM ( 'Longitudinal Data'[Target Audience] )
        ),
        'Control Table'[Metric] = "BTP: Blood Components"
    ) >= 0.895, "#C6EFCE",
    CALCULATE (
        DIVIDE (
            SUM ( 'Longitudinal Data'[Compliant] ),
            SUM ( 'Longitudinal Data'[Target Audience] )
        ),
        'Control Table'[Metric] = "Conflict Resolution"
    ) >= 0.895, "#C6EFCE",
    CALCULATE (
        DIVIDE (
            SUM ( 'Longitudinal Data'[Compliant] ),
            SUM ( 'Longitudinal Data'[Target Audience] )
        ),
        'Control Table'[Metric] = "Fire Safety"
    ) >= 0.895, "#C6EFCE",
    "#FFC7CE"
)

Select the Metric field in the table and then apply conditional formatting:

The final visual effect is shown below :

The pbix file is attached.

If you have any other questions please feel free to contact me.

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

[FarhanJeelani]

Hi @petergarry111 ,

try this:

_Format BG Compliance =
VAR Metric = SELECTEDVALUE('Control Table'[Metric])
VAR ComplianceRate = CALCULATE(
DIVIDE(
SUM('Longitudinal Data'[Compliant]),
SUM('Longitudinal Data'[Target Audience])
)
)
RETURN
SWITCH(
TRUE(),
Metric = "Appraisals" && ComplianceRate >= 0.895, "#C6EFCE",
Metric = "BTP: Blood Components" && ComplianceRate >= 0.895, "#C6EFCE",
Metric = "Conflict Resolution" && ComplianceRate >= 0.895, "#C6EFCE",
Metric = "Fire Safety" && ComplianceRate >= 0.895, "#C6EFCE",
"#FFC7CE"
)

Please mark this as solution if it helps you. Appreciate Kudos