Background Color in Matrix rows

[Prakash1050]

Hi All, 

     I have one matrix visual. 

That matrix row have : Table 1 [Details]
That matrix column have : Calender[Month_Year_Format]

That matrix value have : one measure[Overall_Measure] it returns values of the details by Month_Year_Format

    Then, I create one measure that is : 

Highlight =

 var details = SELECTEDVALUE('Table 1'[Details])

 var result = IF(details="XXX" || details = "YYY",1,0)

 return result

Here, I need to apply conditinal formatting form [Overall_Measure] by the Highlights measure if result = 1, i need to apply the background color in "#F0AF87" if result = 0, i need to apply the background color in "White". This will work properly when the values are present in the [Overall_Measure]. But, if the [Overall_Measure] is blank in any "XXX" and "YYY" rows that background is not changed to "#F0AF87" this color. If the [Overall_Measure] have value that "XXX" and "YYY" rows background color is changed to "#F0AF87" this color.

I need to change the entire rows of "XXX" and "YYY" are changed to "#F0AF87" this color if they have values or not.

How to change the background color?
Please help me.

Thanks in Advance

[v-heq-msft]

Hi @Prakash1050 ,
Thanks for 123abc and Kedar_Pande reply.
Here is what I need to add, according to my tests, custom formatting is not applied to null values when the data in the measure is null. If you want to standardize the format for data that belongs to a class, you can assign 0 to the null value and that will satisfy your requirement. Here is one way to assign 0

Overall_Measure = SELECTEDVALUE('Table'[Value])+0

Final output

Best regards,
Albert He

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

[Prakash1050]

Thanks for the reply.

This measure is fine.
But i have one measure :

Overall_Measure Replacement =

  VAR details = SELECTEDVALUE('Table 1'[Details])

RETURN

        IF(details = "XXX" || details = "YYY",

           IF(ISBLANK([OverallMeasure]), 0, [OverallMeasure]),

           [OverallMeasure]

        )

This is the replacement measure for the Overall_Measure.
Here, blank values are replace to 0. 
But, one issues there in matrix column filed have Calender's Month_Year_Format column.

That column contain Jan-2023 to Dec-2024 but values between May-2023 to Sep-2024 only.
If, I add this mesaure to the matrix for replacement of [Overall_Measure] the values for Oct, Nov, Dec -2024 also showing the matrix in 0. 
So, I don't want to show that values.

Any other way to complete this logic?

[v-heq-msft]

Hi @Prakash1050 ,
If you only want to display values within a particular interval, you can specify the months to display by controlling the filters on the visualization.

Best regards,
Albert He

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

[Prakash1050]

Yes correct @v-heq-msft ,

But, the data will be added regularly. So, i need check each time when the data will be added or not and I change the filter selection on each time.

Is any other way to achieve this logic?

Sorry for adding my doubts again and again.

[v-heq-msft · Accepted solution]

Hi @Prakash1050 ,
If you want to automatically determine if a month is legal, you can create two columns on the table

IsNotBlank = 
IF(
    'Table 2'[Value] <> BLANK(),
    1,
    0
)

IsTrue = 
CALCULATE(
    SUM('Table 2'[IsNotBlank]),
    ALLEXCEPT(
        'Table 2',
        'Table 2'[Month_Year]
    )
)

Create a column on the calendar table

IsExist = 
VAR CurrentMonthYear = 'Calendar'[Calendar]
VAR _isTrue =
IF (
    ISBLANK (
        LOOKUPVALUE (
            'Table 2'[IsTrue],
            'Table 2'[Month_Year], CurrentMonthYear
        )
    ),
    0,
    LOOKUPVALUE (
        'Table 2'[IsTrue],
        'Table 2'[Month_Year], CurrentMonthYear
    )
)
RETURN
IF(
    _isTrue > 0,
    1,
    0
)

Putting isExist into the visualization filter

Set is 1 and final output

Best regards,
Albert He

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

[Prakash1050]

Hi @v-heq-msft ,

    Thank you for this much effort. 
    It fullfil my requirement.

    Sorry, for this much days delayed.

    This is I need. Thank you once again.

[Prakash1050]

Here, the snip of the matrix

Here, Jun 2024 has no values so the background color is not appear. 
But i need to show the background color in blank cell also. 
It is possible?
How to do?
Please help me

[123abc]

Adjust the Highlight Measure: Update the measure to ensure it applies formatting even when there’s no value in Overall_Measure.

Here’s how you can modify your Highlight measure:

DAX

Highlight = VAR details = SELECTEDVALUE('Table 1'[Details]) VAR result = IF(details = "XXX" || details = "YYY", 1, 0) RETURN result

1. Create a Formatting Measure for the Background: Create a new measure to apply the conditional formatting based on the Highlight measure.

   DAX

   BackgroundColor = IF([Highlight] = 1, "#F0AF87", "White")

2. Apply Conditional Formatting in the Matrix Visual:

   • Go to your matrix visual in Power BI.
   • Select the Overall_Measure field within the Values section.
   • Click on Conditional formatting > Background color.
   • Choose Field Value as the format style.
   • In the drop-down menu, select the BackgroundColor measure.

This setup will apply the background color #F0AF87 to the entire row where Details is "XXX" or "YYY," regardless of whether Overall_Measure has values.

[Prakash1050]

No, this measure also don't change the background color. The blank values background still in white color.

[Kedar_Pande]

@Prakash1050 

Update the Highlight Measure

Highlight = 
VAR details = SELECTEDVALUE('Table 1'[Details])
VAR hasValue = NOT(ISBLANK([Overall_Measure]))
RETURN 
IF(details = "XXX" || details = "YYY" || (details = "XXX" || details = "YYY" && NOT HASONEVALUE('Table 1'[Details]) && NOT hasValue), 1, 0)

In the conditional formatting dialog, set the rules:

• If Highlight equals 1, set the background color to #F0AF87.
• If Highlight equals 0, set the background color to White.

💌 If this helped, a Kudos 👍 or Solution mark would be great! 🎉
Cheers,
Kedar
Connect on LinkedIn

[Prakash1050]

No, this measure also don't change the background color of the blank values.