Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Anonymous
Not applicable

Conditional formatting not working on measure

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.

PBIConditionalFormattingProblem.JPG

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

 

Any ideas how to get the Measure1 working with ConditionlFormatting?

 

 

 
1 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.

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

8 REPLIES 8
natabird3
Continued Contributor
Continued Contributor

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
Community Support
Community Support

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))

1.png

 

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
Not applicable

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?

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.

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



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? 

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.


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



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. 

 

Conditional Formatting not working on measure.jpg

 

 

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.


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.