topic Re: Conditional Formatting- Background color based on comparing the values of multiple columns in DAX Commands and Tips

Conditional Formatting- Background color based on comparing the values of multiple columns

third_hicana — Tue, 06 Feb 2024 10:54:47 GMT

Hi. I just want to ask for help on how to create a measure to format the background color based on the lowest to the highest value of multiple columns.

Thank you for your help

Re: Conditional Formatting- Background color based on comparing the values of multiple columns

lbendlin — Wed, 07 Feb 2024 00:25:12 GMT

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

Re: Conditional Formatting- Background color based on comparing the values of multiple columns

third_hicana — Wed, 07 Feb 2024 03:07:14 GMT

Hi lbendlin Here's sample data. Thanks

BOP ID BOP Title Area Department Code Q1 Q2 Q3 Q4 Entire duration

B003	Project 1	Operations	1	0.33	0.7	0.67	1
B003	Project 1	Operations	2	0.07	2	0.27
B003	Project 1	Operations	3					3
B003	Project 1	Operations	4					1
B003	Project 1	Operations	5	1
B003	Project 1	Operations	6			0.7
B003	Project 1	Operations	7		1		0.33
B003	Project 1	Operations	8	0.5	2.5
B003	Project 1	Operations	9			0.25
B003	Project 1	Operations	10	1
B003	Project 1	Operations	11					1
B003	Project 1	Operations	12					2
B003	Project 1	Operations	13					2.5
B003	Project 1	Operations	14					3.2
B003	Project 1	Operations	15		1
B003	Project 1	Operations	16			0.5

Re: Conditional Formatting- Background color based on comparing the values of multiple columns

v-weiyan1-msft — Wed, 07 Feb 2024 11:02:28 GMT

Hi third_hicana ,

I am not sure if I understood your question correctly.
Based on your description and my understanding, I used the following data sample.

Please refer to the following steps:
1.Please try code as below to Create a Calcualted table.

Table 2 = DATATABLE( "ColumnName",STRING, "Order",INTEGER, { {"Est. FTE Q1",1}, {"Est. FTE Q2",2}, {"Est. FTE Q3",3}, {"Est. FTE Q4",4}, {"Est. FTE for Initiative Duration",5} } )

2. Use the following code to create a Measure.

Measure = VAR Q1 = SELECTEDVALUE('Table'[Est. FTE Q1]) VAR Q2 = SELECTEDVALUE('Table'[Est. FTE Q2]) VAR Q3 = SELECTEDVALUE('Table'[Est. FTE Q3]) VAR Q4 = SELECTEDVALUE('Table'[Est. FTE Q4]) VAR Initiative_Duration = SELECTEDVALUE('Table'[Est. FTE for Initiative Duration]) RETURN SWITCH( MAX('Table 2'[ColumnName]), "Est. FTE Q1",Q1, "Est. FTE Q2",Q2, "Est. FTE Q3",Q3, "Est. FTE Q4",Q4, "Est. FTE for Initiative Duration",Initiative_Duration )

3.The fields in the visual object are as follows, right-click "Measure" and select "Conditional Formatting". Then do the following.

Result is as below.

Please correct me if I misunderstood your needs.

Best Regards,
Yulia Yan

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

Re: Conditional Formatting- Background color based on comparing the values of multiple columns

third_hicana — Mon, 12 Feb 2024 06:28:26 GMT

Hi v-weiyan1-msft

Is there anyway to do it on a table instead of a matrix? columns that I need to include aside from FTEs. Thank you.