Apply Colors to selected range in matrix

[vin26]

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. Below is the example:

Please let me know is there any way to achieve above requirement and also please suggest any other way to represent data in similar way.

[Greg_Deckler]

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]

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 · Accepted solution]

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]

Is there any way to summarize the values up by color (Total or Orange, Total of Green.. etc.)?, I have been trying to replicate the measure and trying to create a summarized table, unfortunately matrix is not allowing me to drop measure to rows or column.

[vin26]

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