conditional background color for specific complete columns in the matrix

[Anonymous]

Hi,

I want the background color for complete 3 columns in my matrix in this way as shown below where my condition would be like- if Table(percentage) = "D(75-90%)" then "yellow" and so on. But I'm not able to do it using conditional formatting. Can someone tell the measure I should use?

When I'm using the measure:

COLOUR =

IF (HASONEFILTER(FinalManipulatedTable[Percentage])

,IF( FIRSTNONBLANK('FinalManipulatedTable'[Percentage] , 0) = "D(75-90%)"

,"yellow"

,IF( FIRSTNONBLANK('FinalManipulatedTable'[Percentage] , 0) = "E(90-100%)"

,"orange"

,IF( FIRSTNONBLANK('FinalManipulatedTable'[Percentage] , 0) = "Z>100"

,"red"

,"white"

)))

,BLANK()

)

Its not including the blank cells in color and showing the below matrix:

Please help!

[Anonymous]

@Anonymous 

Emm, or can you share the pbix file after removing or changing sensitive data?

Paul Zheng _ Community Support Team

[PaulDBrown]

@Anonymous 

You can achive this be referencing the column directly in your measure. Here is an example in a matrix with "Channel" from (DIm Channel Table) are the columns and two measures for values (Sum of Sales and target)

Using this measure as for the conditional formatting:

Column colour = SWITCH(TRUE(),
    MAX('DIM Channel'[Channel]) = "B", "Red",
    MAX('DIM Channel'[Channel]) = "D", "Green")

[Anonymous]

Its giving weird results. Please see:

Am I missing something?

[PaulDBrown]

@Anonymous 

OK, here is way to colour the blanks. First create a new measure to substitute blanks with 0 using a measure along the lines of:

no blanks measure = [your measure] + 0

In my example:

Sum of Sales + 0 = [Sum of Sales] + 0

Use this measure in the matrix and the measure for the conditional column. In my example:

Column colour = SWITCH(TRUE(),
    MAX('DIM Channel'[Channel]) = "B", 1,
   MAX('DIM Channel'[Channel]) = "D", 2)

And you will get this:

If you want to show blanks instead of 0, create a new measure to use for the conditional formatting for the font referecning the original measure (which returns blanks):

0 value condit column = 
SWITCH(TRUE(),
    AND(ISBLANK([Sum of Sales]), MAX('DIM Channel'[Channel]) = "B"), 1,
    AND(ISBLANK([Sum of Sales]), MAX('DIM Channel'[Channel]) = "D"), 2,
     ISBLANK([Sum of Sales]), 3)

Now use this measure in the conditional formatting for the font:

[Anonymous]

I don't know why but any measure is not replacing blanks with anything, even if I add 0. Thanks for the solutions though. 

[PaulDBrown]

@Anonymous 

Can you show the DAX for the original measure and the + 0 measure and the result in the visual?

[Anonymous]

Originally I'm not using any measure. I'm just using count of a column in values as you can see in the below image. So I am not able to create this measure you talked about:

no blanks measure = [your measure] + 0

[PaulDBrown]

@Anonymous 

Use a measure instead.

Count Tasks = COUNT(table[Task]) 

and take it from there

[Anonymous]

No change in the result even after I add 0 to measure. Its not replacing the blank cells.

[amitchandak]

@Anonymous , you need check blank for the measure too

,IF( FIRSTNONBLANK('FinalManipulatedTable'[Percentage] , 0) = "D(75-90%)"  && (isblank([Mesure]) || not(isblank([measure]))) ,"yellow"

[Anonymous]

Did not work. Still showing the same result. Is there any other way?

[Anonymous]

Hi,

But I don't have any measure to put in (isblank([Measure]) since i'm using a column name of the table 'FinalManipulatedTable'[Percentage] to create this measure 'colour'.

[amitchandak]

@Anonymous , use min('FinalManipulatedTable'[Percentage]) in place of that and check.

What measure is displayed/used  in values of a matrix, use that