Forum Discussion
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. 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.
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,
5 Replies
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.
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?
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,
