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] ) )
)

  • lbendlin's avatar
    lbendlin
    Super User
    2 years ago

    I refactored the code. Seems to work better now.

     

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

     

     

     

    • powerbi2srm's avatar
      powerbi2srm
      Resolver II
      2 years ago

      That's perfect! Thank you so much for your time.

    • powerbi2srm's avatar
      powerbi2srm
      Resolver II
      2 years ago

      Hi lbendlin! I just realized if I sort year column by other one, the code doesn't work properly. 
      I want to sort years inversely as you can see in the next image:


      How can I solve this? I doesn't understand why doesn't run. Thank you so much.