Forum Discussion

vin26's avatar
vin26
Icon for Resolver I rankResolver I
6 years ago
Solved

Apply Colors to selected range in matrix

Hello, I need a help in creating table with custom color selection. My requirement is to create a table or matrix, in which user should be able select range and apply the color on selected range. Be...
Need Help
Show and Tell
Tips and Tricks
  • v-lid-msft's avatar
    v-lid-msft
    6 years ago

    Hi vin26 ,

     

    We can try to create ten calculated table first as the range:

     

    For Rows:

     

    C_Red_Row = DISTINCT('Table'[Row])

     

     

     

    C_Yellow_Row = DISTINCT('Table'[Row])

     

     

     

    C_Green_Row = DISTINCT('Table'[Row])

     

     

     

    C_Blue_Row = DISTINCT('Table'[Row])

     

     

     

    C_Gray_Row = DISTINCT('Table'[Row])

     

     

    For Columns:

     

    C_Red_Col = DISTINCT('Table'[Column])

     

     

     

    C_Yellow_Col = DISTINCT('Table'[Column])

     

     

     

    C_Green_Col = DISTINCT('Table'[Column])

     

     

     

    C_Blue_Col = DISTINCT('Table'[Column])

     

     

     

    C_Gray_Col = DISTINCT('Table'[Column])

     

     

    Then we can create a measure as Filed Value of conditional format:

     

    Measure:

     

    Color Measure = 
SWITCH (
    TRUE (),
    MIN ( 'Table'[Row] ) IN DISTINCT ( C_Red_Row[Row] )
        && MIN ( 'Table'[Column] ) IN DISTINCT ( C_Red_Col[Column] ), "#FF0000",
    MIN ( 'Table'[Row] ) IN DISTINCT ( C_Yellow_Row[Row] )
        && MIN ( 'Table'[Column] ) IN DISTINCT ( C_Yellow_Col[Column] ), "#FFFF00",
    MIN ( 'Table'[Row] ) IN DISTINCT ( C_Green_Row[Row] )
        && MIN ( 'Table'[Column] ) IN DISTINCT ( C_Green_Col[Column] ), "#00FF00",
    MIN ( 'Table'[Row] ) IN DISTINCT ( C_Blue_Row[Row] )
        && MIN ( 'Table'[Column] ) IN DISTINCT ( C_Blue_Col[Column] ), "#0000FF",
    MIN ( 'Table'[Row] ) IN DISTINCT ( C_Gray_Row[Row] )
        && MIN ( 'Table'[Column] ) IN DISTINCT ( C_Gray_Col[Column] ), "#D0D0D0"
)

     

     

    Please remember to close the interactions between different slicers so that you can choose value smoothly

     

     


    If it doesn't meet your requirement, Could you please show the exact expected result based on the tables that we have shared?


    By the way, PBIX file as attached.


    Best regards,

     

Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
6 years ago

Right, so you would have to create a measure that would take those slicer values and return a number indicator for a color. You would use this measure in your conditional formatting rules.

  • vin26's avatar
    vin26
    Icon for Resolver I rankResolver I
    6 years ago

    Thanks Greg, Yes, you are right. I am trying to create the measure to get the all the slicer selections into one measure, somewhat it is not working. Could you please suggest idea?

    • v-lid-msft's avatar
      v-lid-msft
      Icon for Community Support rankCommunity Support
      6 years ago

      Hi vin26 ,

       

      We can try to create ten calculated table first as the range:

       

      For Rows:

       

      C_Red_Row = DISTINCT('Table'[Row])

       

       

       

      C_Yellow_Row = DISTINCT('Table'[Row])

       

       

       

      C_Green_Row = DISTINCT('Table'[Row])

       

       

       

      C_Blue_Row = DISTINCT('Table'[Row])

       

       

       

      C_Gray_Row = DISTINCT('Table'[Row])

       

       

      For Columns:

       

      C_Red_Col = DISTINCT('Table'[Column])

       

       

       

      C_Yellow_Col = DISTINCT('Table'[Column])

       

       

       

      C_Green_Col = DISTINCT('Table'[Column])

       

       

       

      C_Blue_Col = DISTINCT('Table'[Column])

       

       

       

      C_Gray_Col = DISTINCT('Table'[Column])

       

       

      Then we can create a measure as Filed Value of conditional format:

       

      Measure:

       

      Color Measure = 
SWITCH (
    TRUE (),
    MIN ( 'Table'[Row] ) IN DISTINCT ( C_Red_Row[Row] )
        && MIN ( 'Table'[Column] ) IN DISTINCT ( C_Red_Col[Column] ), "#FF0000",
    MIN ( 'Table'[Row] ) IN DISTINCT ( C_Yellow_Row[Row] )
        && MIN ( 'Table'[Column] ) IN DISTINCT ( C_Yellow_Col[Column] ), "#FFFF00",
    MIN ( 'Table'[Row] ) IN DISTINCT ( C_Green_Row[Row] )
        && MIN ( 'Table'[Column] ) IN DISTINCT ( C_Green_Col[Column] ), "#00FF00",
    MIN ( 'Table'[Row] ) IN DISTINCT ( C_Blue_Row[Row] )
        && MIN ( 'Table'[Column] ) IN DISTINCT ( C_Blue_Col[Column] ), "#0000FF",
    MIN ( 'Table'[Row] ) IN DISTINCT ( C_Gray_Row[Row] )
        && MIN ( 'Table'[Column] ) IN DISTINCT ( C_Gray_Col[Column] ), "#D0D0D0"
)

       

       

      Please remember to close the interactions between different slicers so that you can choose value smoothly

       

       


      If it doesn't meet your requirement, Could you please show the exact expected result based on the tables that we have shared?


      By the way, PBIX file as attached.


      Best regards,

       

      • vin26's avatar
        vin26
        Icon for Resolver I rankResolver I
        6 years ago

        v-lid-msft Amazing!, you are fantastic, it works. Thanks a lot for the solution!