Conditional Formatting on Dynamic Measures(Parameter based)

[Prade]

I have a matrix visual which displays sales for the last 12 weeks per region per product. You can imagine it as region and product are on rows section, week on column section, sales measure on values section. Now there is a new requirment. I was asked to display the first 6 weeks and next weeks sales separately on the same visual. Since it is not directly possible, I have created 12 measure per 12 weeks, 13th measure for first 6weeks and 14th measure for next 6 weeks.Finally I have created a parameter combining all these measure with dynamic names as well. After placing this parameter based output on the visual, I got another requirement to add conditional formatting on each value based on some conditions. I am not finding the conditional formatting option on matrix visual since I am using paramter based output. Is there any way that I can achieve the requirement ensuring all the 3 conditions([12 weeks data + first 6 weeks and next 6 weeks on same visual] , dynamic names for all measures, conditional formatting for all the cells)

[hnguy71 · Accepted solution]

Hi @Prade ,

Hmmm, seeing you have 14 measures in total seems a bit odd. I would try to rework your measures. At most, you should only have 3 measures.

However, you should be able to do conditional formatting based on each indivdiual measure. Do you see this cell elements option?

In my test, I am using a parameter field and also measures and can see the option.

[Ritaf1983 · Accepted solution]

Hi @Prade 

If I understood the problem correctly, and it involves a measure that is dynamic and needs to be conditionally formatted based on both the 'column name' and the value of the measure, you can create a DAX formula for conditional formatting.

In addition to the translation, please provide an example of a measure that returns a color and uses SELECTEDVALUE to demonstrate conditional formatting based on both the column and the value of any given measure.

Example of a DAX measure for conditional formatting using SELECTEDVALUE:

MeasureColor =
SWITCH(
TRUE(),
SELECTEDVALUE('Table'[Column]) = "Category1" && [Measure] > 100, "Red",
SELECTEDVALUE('Table'[Column]) = "Category1" && [Measure] <= 100, "Green",
SELECTEDVALUE('Table'[Column]) = "Category2" && [Measure] > 50, "Blue",
SELECTEDVALUE('Table'[Column]) = "Category2" && [Measure] <= 50, "Yellow",
"Gray"
)

In this example:

The SELECTEDVALUE('Table'[Column]) checks the selected column value.
The conditions are based on both the column name (e.g., "Category1", "Category2") and the value of the [Measure].
The formula returns a color (as a string) depending on the conditions.

You can use this measure for conditional formatting in a visual by applying it to the "Background color" or "Font color" under the "Conditional formatting" settings.

If you're using Field Parameters and now need to create a conditional formatting rule that is based both on the selection of the parameter and on the content of the measure, here's how you should proceed:

Step-by-Step Solution:
Create a Duplicate Column: Field parameters in Power BI behave as metadata layers and can't directly be used for conditional formatting. To apply conditional formatting, you'll need to duplicate the parameter column. This duplicate column will act as a regular column that Power BI recognizes for applying conditional rules.

You can create this duplicate by selecting the same fields used in the Field Parameter but as regular columns or measures in your data model.
Define Conditional Formatting Logic: Once you have the duplicate column in place, define your conditional logic. You can use a DAX formula like SWITCH or IF to create the necessary logic based on both the parameter selection and the content of the measure. For example, you might use SELECTEDVALUE to detect which measure or field is selected from the field parameter and apply different conditions accordingly.

Here's an example of a DAX measure that handles conditional formatting:

DAX
Copy code
MeasureColor =
SWITCH(
TRUE(),
SELECTEDVALUE('FieldParameter'[Selection]) = "Sales" && [SalesMeasure] > 1000, "Green",
SELECTEDVALUE('FieldParameter'[Selection]) = "Sales" && [SalesMeasure] <= 1000, "Red",
SELECTEDVALUE('FieldParameter'[Selection]) = "Margin" && [MarginMeasure] > 20, "Blue",
SELECTEDVALUE('FieldParameter'[Selection]) = "Margin" && [MarginMeasure] <= 20, "Yellow",
"Gray"
)

video guide for more information:

https://www.youtube.com/watch?v=YEB5sezlPE4

If it is something else

please share a pbix or some dummy data that keep the raw data structure with expected results? It should help us clarify your scenario and test to coding formula.

How to Get Your Question Answered Quickly  

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly

[Anonymous · Accepted solution]

Hi @Prade ,

Is your issue that when you use field parameter for your measure in matrix value, you couldn't find the conditional formatting?

On myside, I create a sample for a test, I think you can use this function in Format > Cell elements.

Remove the filter in your slicer then you can see all measures.

You can also download the latest Power BI Desktop Version and try again.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Ritaf1983 · Accepted solution]

Hi @Prade 

If I understood the problem correctly, and it involves a measure that is dynamic and needs to be conditionally formatted based on both the 'column name' and the value of the measure, you can create a DAX formula for conditional formatting.

In addition to the translation, please provide an example of a measure that returns a color and uses SELECTEDVALUE to demonstrate conditional formatting based on both the column and the value of any given measure.

Example of a DAX measure for conditional formatting using SELECTEDVALUE:

MeasureColor =
SWITCH(
TRUE(),
SELECTEDVALUE('Table'[Column]) = "Category1" && [Measure] > 100, "Red",
SELECTEDVALUE('Table'[Column]) = "Category1" && [Measure] <= 100, "Green",
SELECTEDVALUE('Table'[Column]) = "Category2" && [Measure] > 50, "Blue",
SELECTEDVALUE('Table'[Column]) = "Category2" && [Measure] <= 50, "Yellow",
"Gray"
)

In this example:

The SELECTEDVALUE('Table'[Column]) checks the selected column value.
The conditions are based on both the column name (e.g., "Category1", "Category2") and the value of the [Measure].
The formula returns a color (as a string) depending on the conditions.

You can use this measure for conditional formatting in a visual by applying it to the "Background color" or "Font color" under the "Conditional formatting" settings.

If you're using Field Parameters and now need to create a conditional formatting rule that is based both on the selection of the parameter and on the content of the measure, here's how you should proceed:

Step-by-Step Solution:
Create a Duplicate Column: Field parameters in Power BI behave as metadata layers and can't directly be used for conditional formatting. To apply conditional formatting, you'll need to duplicate the parameter column. This duplicate column will act as a regular column that Power BI recognizes for applying conditional rules.

You can create this duplicate by selecting the same fields used in the Field Parameter but as regular columns or measures in your data model.
Define Conditional Formatting Logic: Once you have the duplicate column in place, define your conditional logic. You can use a DAX formula like SWITCH or IF to create the necessary logic based on both the parameter selection and the content of the measure. For example, you might use SELECTEDVALUE to detect which measure or field is selected from the field parameter and apply different conditions accordingly.

Here's an example of a DAX measure that handles conditional formatting:

DAX
Copy code
MeasureColor =
SWITCH(
TRUE(),
SELECTEDVALUE('FieldParameter'[Selection]) = "Sales" && [SalesMeasure] > 1000, "Green",
SELECTEDVALUE('FieldParameter'[Selection]) = "Sales" && [SalesMeasure] <= 1000, "Red",
SELECTEDVALUE('FieldParameter'[Selection]) = "Margin" && [MarginMeasure] > 20, "Blue",
SELECTEDVALUE('FieldParameter'[Selection]) = "Margin" && [MarginMeasure] <= 20, "Yellow",
"Gray"
)

video guide for more information:

https://www.youtube.com/watch?v=YEB5sezlPE4

If it is something else

please share a pbix or some dummy data that keep the raw data structure with expected results? It should help us clarify your scenario and test to coding formula.

How to Get Your Question Answered Quickly  

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly

[hnguy71 · Accepted solution]

Hi @Prade ,

Hmmm, seeing you have 14 measures in total seems a bit odd. I would try to rework your measures. At most, you should only have 3 measures.

However, you should be able to do conditional formatting based on each indivdiual measure. Do you see this cell elements option?

In my test, I am using a parameter field and also measures and can see the option.