Conditional formatting not working on measure

[Anonymous]

I used EnterData in PBIDesktop and put just 3 rows there in just 1 column - Column1. I then made a Measure1 = SUM(Table1[Column1])

When I put Column1 in a table and add condional formatting to it, it works fine. In the screenshot below, you can see that "-17" is in red and "1" and "12" are in blue - as expected.

However, when I put Measure1 in a table and add same condional formatting to it, it does not work. As you can see in the screenshot below, the "-4" should have been in red color.

My PBIDesktop version is 2.65.* - which is a Dec2018 release

Any ideas how to get the Measure1 working with ConditionlFormatting?

[natabird3]

Not working for me either, i tried to add conditional formating on a matrix when using a measure and it was not applying the formating properly. Any ideas when this would be fixed, seems a basic feature?

[v-juanli-msft]

Hi @Anonymous

As tested with the lastest version 2.66.5376.1681 64-bit (February 2019)

since when you add only one measure in the table, it is aggregated.

Conditional formatting doesn't support for any aggregation value.

As a workaround, i create a measure2, 

Measure 2 = CALCULATE(SUM(Table1[Column1]),ALL(Table1))

Best Regards

Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thanks for responding @v-juanli-msft

I tried in my real dashboard. I have 3 measures as below:

• msr_TotalSalary&Wages_Actual = CALCULATE( SUM(vAcctSumryBS[Dr_Cr]) , vAcctSumryBS[Category] ="Salary & Wages")
• msr_TotalSalary&Wages_Forecast = CALCULATE( SUM(vAcctSumryBS[ForecastAmt]) , vAcctSumryBS[Category] ="Salary & Wages")
• msr_TotalSalary&Wages_Variance_AvF = [msr_TotalSalary&Wages_Actual] - [msr_TotalSalary&Wages_Forecast]

  The value of msr_TotalSalary&Wages_Variance_AvF is negative. When I apply rule based formatting to it, it still does not change its color. I have checked it in Desktop Feb release as well. I tried it against a high number for msr_TotalSalary&Wages_Actual as well. It does not change colors. 

  Your expression for your measure is similar to mine, and yet I do not get the colors. Only difference I see is that you are using ALL. 

  Can you please help again?

[MFelix · Accepted solution]

Hi @Anonymous  and @natabird3 

I was responding to the post below and got your post also.

https://community.powerbi.com/t5/Desktop/Conditional-Formatting-Quirks/m-p/929788/highlight/false#M445561

As refered by @v-juanli-msft mMeasure are based on context so when you have the calculation of the measure without reference to any other calcuttion the aggregation doesn't return any background colour.

Believe that is the behaviour of the table visualization since you can add different levels of context and by default it's summarized with totals if you had an additional level of context as it's refered by adding the column 1 the formatting is applied.

If you use the condittional formatting on a card or a bar chart since the context is based on the slicer and on that visualization it will work correctly.

I agree that this is not user friendly and doesn't make sense. I will put an issue on the forum refering to this.

[sandermatthijs]

Don't mean to pester you.. Being lazy; rather than check it for myself I wil ask you: have you gotten any tracktion on the "I will put an issue on the forum refering to this."? 😁 Is this a recognised issue and is there a pending fix? 

[MFelix]

Hi @sandermatthijs ,

This is posted on the following post https://community.powerbi.com/t5/Issues/Condittional-formatting-table-using-measures/idi-p/929891.

Although they didn't answer this question the problem is currently solved.

I downloaded the original file I used for the post and is working properly

Check PBI file attach.

[sandermatthijs]

Interesting. I also did a test with Powerbi regular Desktop June 2020 (aka Cloud? I get so confused by the naming of these versions!) versus the on-prem Server Desktop Jan-2020 verion (aka "black icon"). 

Result: Works on June-2020, does not work on Server Jan-2020. Hope Server gets that fixed soon. 

[MFelix]

Hi @sandermatthijs ,

For now the two versions aren't aligned (server vs desktop) the main developments are made on the Desktop version and then pass to the server.

Some time back there were plans to only have a single version so no need to have two programs installed, but I haven't heard anything else about it, what I can say is that server is cacthing up so maybe on a next update they solve that issue.