topic Re: Switch Measure - conditional formatting in DAX Commands and Tips

Switch Measure - conditional formatting

cn4422 — Wed, 19 Feb 2025 10:50:38 GMT

Hi,

I have several switch-measures in a matrix table, like this example here:

Switch = SWITCH(SELECTEDVALUE(Countries[Country]),

"AT",DIVIDE([G AT],[L AT],0),

"BE",DIVIDE([G BE],[L BE],0),

"CH",DIVIDE([G CH],[L CH],0),

)

Is it possible to apply conditional formatting with a three-color gradient to each row individually, so that each row (only values, no sum) is evaluated in its own context rather than across all rows?

Many thx in advance!

Re: Switch Measure - conditional formatting

bhanu_gautam — Wed, 19 Feb 2025 11:42:31 GMT

cn4422 ,

Create a Measure for Conditional Formatting: You need to create a measure that will be used for the conditional formatting. This measure should return a value that can be used to determine the color gradient.

ConditionalFormattingMeasure =
SWITCH(
TRUE(),
SELECTEDVALUE(Countries[Country]) = "AT", DIVIDE([G AT], [L AT], 0),
SELECTEDVALUE(Countries[Country]) = "BE", DIVIDE([G BE], [L BE], 0),
SELECTEDVALUE(Countries[Country]) = "CH", DIVIDE([G CH], [L CH], 0)
)

Apply Conditional Formatting: Once you have the measure, you can apply conditional formatting to the matrix table.

Go to the matrix visual in your Power BI report.
Click on the dropdown arrow next to the measure you want to format.
Select "Conditional formatting" and then "Background color" or "Font color" depending on your preference.
In the conditional formatting dialog, choose "Field value" and select the ConditionalFormattingMeasure you created.
Set the color scale to a three-color gradient and configure the minimum, midpoint, and maximum values as needed.

Re: Switch Measure - conditional formatting

cn4422 — Thu, 20 Feb 2025 12:20:38 GMT

Hi bhanu_gautam,

thanks for your reply and the provided measure and explanation!

The conditional formatting itself works fine.

However, it considers all rows from all countries, using the highest and lowest values across all countries as reference points.

What I would like instead is for each row to reference only itself. In this case, we have only two rows—so for AT, the lowest value would be €47.19, and the highest would be €55.58, regardless of the other rows.

Is there a way to apply conditional formatting in this way?

Re: Switch Measure - conditional formatting

bhanu_gautam — Thu, 20 Feb 2025 13:36:47 GMT

cn4422 , Not sure but trying separate measure than and use it

reate Individual Measures for Each Country: Create separate measures for each country that calculate the ratio or value you want to use for conditional formatting.
G_AT_L_AT_Ratio = DIVIDE([G AT], [L AT], 0)
G_BE_L_BE_Ratio = DIVIDE([G BE], [L BE], 0)
G_CH_L_CH_Ratio = DIVIDE([G CH], [L CH], 0)
Apply Conditional Formatting: Apply conditional formatting to each measure separately in the matrix visual.

Go to the matrix visual in your Power BI report.
Click on the dropdown arrow next to the measure you want to format (e.g., G_AT_L_AT_Ratio).
Select "Conditional formatting" and then "Background color" or "Font color" depending on your preference.
In the conditional formatting dialog, choose "Field value" and select the corresponding measure (e.g., G_AT_L_AT_Ratio).
Set the color scale to a three-color gradient and configure the minimum, midpoint, and