Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Conditional Formatting - Background with Rules

I have a situation where the users want Gross Sales to be blacked out for certain products since there is no data.

 

I created custom columns to identify this situation.

Gross Sales Flag = if('table'[column]=2,1,0)
Product Flag = if('table'[Product]="abc",1,if('table'[Product]="xyz",1,0))
 
I then Created a measure I could check when applying the conditional formatting
Gross Sales Product = max('table'[Gross Sales Flag]) + Max('table'[Product]) - report is at this level.
 
Conditional Formatting
 
However, since the measure value is blank(), it won't apply the formatting according to this rule.
 
I attempted to change my blank() to 0 when the Gross Sales Product=2, but it still doesn't work 100% of the time.
 
Any ideas? 
 
Need Help

3 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      My report looks like this. the part with Red around it is what they want blanked out. There will never be any data

      The users want it to look like this

      I don't want every place to be black if blank, just this spot as indicated in orginal post. That is why I did the custom columns and measure.

       

      When I change the measure to put " " instead of blank(), it worked in all but ONE case. I figured out, it doesn't work if there is NO row in the data base (with 0 or null).  The intersection on the matrix is where they want it blacked out.

       

      I had played with the measures to get that far.

  • jsh121988's avatar
    jsh121988
    Microsoft Employee
    7 years ago

    You could possibly do format by field, and have a measure output the color values like this:

    _dm_Per_MeasureFormatColors = 
SWITCH(TRUE(),
    [_dm_Per_Measure] >= 0 && [_dm_Per_Measure] < .8, "#fd625e", // Red
    [_dm_Per_Measure] >= .8 && [_dm_Per_Measure] < .97, "#f2c80f", // Yellow
    [_dm_Per_Measure] >= .97, "#34c6bb", // Green
    "#eaeaea"
)

    You can basically implement any logic you want into it, and it centralizes the formatting so you only have to make changes in a single location.