Forum Discussion
Apply Colors to selected range in matrix
- 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,
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,
- vin266 years ago
Resolver I
v-lid-msft Amazing!, you are fantastic, it works. Thanks a lot for the solution!
- vin266 years ago
Resolver I
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.