Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Row by row Conditional Formatting on a Matrix

Dear all,    I have a matrix formating issue.  I want to conditionally format my matirx row by row to show the monthly highest and smallest values for the years and months selected. The result sh...
lbendlin's avatar
lbendlin
Super User
3 years ago

See attached.

 

color =
SWITCH(
    TRUE(),
    ISINSCOPE( 'Date Table'[Day Number] ),
        VAR a =
            CALCULATETABLE(
                SUMMARIZE(
                    'Date Table',
                    'Date Table'[Year],
                    'Date Table'[Month],
                    'Date Table'[Day Number],
                    "sd", [sum data]
                ),
                REMOVEFILTERS(
                    'Date Table'[Year],
                    'Date Table'[Month],
                    'Date Table'[Day Number]
                )
            )
        RETURN
            DIVIDE( [sum data] - MINX( a, [sd] ), MAXX( a, [sd] ) - MINX( a, [sd] ) ),
    ISINSCOPE( 'Date Table'[Month] ),
        VAR a =
            CALCULATETABLE(
                SUMMARIZE(
                    'Date Table',
                    'Date Table'[Year],
                    'Date Table'[Month],
                    "sd", [sum data]
                ),
                REMOVEFILTERS( 'Date Table'[Year], 'Date Table'[Month] )
            )
        RETURN
            DIVIDE( [sum data] - MINX( a, [sd] ), MAXX( a, [sd] ) - MINX( a, [sd] ) ),
    ISINSCOPE( 'Date Table'[Year] ),
        VAR a =
            CALCULATETABLE(
                SUMMARIZE( 'Date Table', 'Date Table'[Year], "sd", [sum data] ),
                REMOVEFILTERS( 'Date Table'[Year] )
            )
        RETURN
            DIVIDE( [sum data] - MINX( a, [sd] ), MAXX( a, [sd] ) - MINX( a, [sd] ) )
)

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Hi Ibendlin, 

 

Thank you so much for the time spent coming up with the above DAX. I appreciate your efort and time. Unfortunately, it doesn't work quite as I expect. In each row the data colouring doesn't flow from smallest to largest. 

I created a work around with the below DAX. I essentially created two of the below DAX, one for formating the monthly view and another for formating the daily view and then added buttons for the use to move between the two matrices rather than drilling up or down. The below DAX is the monthly formating. 

month Row by row CF =

VAR SummaryTable =

    CALCULATETABLE(

        ADDCOLUMNS (

            Summarize( 

                'Data Table name',

                'Data Table name'[row granularity column],

                'Date Table'[Year],

                 'Date Table'[Month]

              

            ),

        " SummaryTable column name", [sum measure of interest]

        ),

        ALLSELECTED('Date Table') // column to remove filter so restart every row

    )

 

VAR MaxValue =

    MAXX (

        SummaryTable,

        [SummaryTable column name]

    )

 

VAR MinValue =

    MINX (

        SummaryTable,

        [SummaryTable column name]

    )

 

VAR range = MaxValue - MinValue

 

VAR hue =

    Round(

        DIVIDE (

        [sum measure of interest] - MinValue,

        range

        )*60, 0

    ) // + 240 // - to change the colour hue

   

VAR colour = "hsla(" & hue & ", " & "100%" & ", " &  "70%" & ", " & 1 & ")"

 

Return

colour


This solution is from Bas, How to Power BI video titled Unleash the full Potential of Conditional Formating- row by row colour scale in a Matrix.