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

     



     

    Background Color in Matrix rows.pbix43 KB
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

 

 

Background Color in Matrix rows.pbix32 KB
  • 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

       

      • Prakash1050's avatar
        Prakash1050
        Helper III
        1 year ago

        Yes correct Anonymous ,

        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.