Forum Discussion

Prakash1050's avatar
Prakash1050
Helper III
1 year ago
Solved

Background Color in Matrix rows

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
Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    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

     



     

11 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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's avatar
      Prakash1050
      Helper III
      1 year ago

      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?
      • Anonymous's avatar
        Anonymous
        Not applicable
        1 year ago

        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

         

  • Kedar_Pande's avatar
    Kedar_Pande
    Super User
    1 year ago

    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's avatar
      Prakash1050
      Helper III
      1 year ago

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

  • 123abc's avatar
    123abc
    Community Champion
    1 year ago

    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's avatar
      Prakash1050
      Helper III
      1 year ago

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

  • Prakash1050's avatar
    Prakash1050
    Helper III
    1 year ago

    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