Creating a Slicer based on Conditionally Format Colors

Introduction:

Have you ever encountered a scenario where you apply conditional formatting and then need to filter based on those colors? You likely want to achieve this without altering established relationships or introducing new ones. In this blog post, we'll explore how to create a slicer that allows you to filter based on these colors, using a custom table and DAX measures.

Step 1: Create a Color Category Table

We need a separate table to map the color names to their respective HEX codes

Color Category = 
DATATABLE(
    "ColorName", STRING,
    "HexCode", STRING,
    "SortOrder", INTEGER,
    {
        { "Green", "#9fdaa4", 1},
        { "Blue", "#8a9fc8", 2},
        { "Yellow", "#ffee80", 3},
        { "Red", "#ff8082", 4},
        {"White", "#FFFFFF", 5} 
    }
)

This table will act as the foundation for your slicer, listing each color with its corresponding HEX code.

Step 2: Define the Color-Logic in Your Measures

Next, you’ll set up the logic that determines which color should be applied based on the values in your visual (Regular conditional formatting steps)

Sales Amount Color = 
SWITCH(
    TRUE(),
    [Sales Amount by Due Date] >= 3000000, "#9fdaa4", // Green
    [Sales Amount by Due Date] >= 2000000 && [Sales Amount by Due Date] <= 3000000, "#8a9fc8", // Blue
    [Sales Amount by Due Date] >= 1000000 && [Sales Amount by Due Date] < 2000000, "#ffee80", // Yellow
    [Sales Amount by Due Date] < 1000000 && [Sales Amount by Due Date] >= 0, "#ff8082", // Red
    "#FFFFFF" // White (for N/A or other cases)
)

Step 3: Creating the Filtered Measure for the Slicer

To dynamically filter your table based on the selected colors, you need a measure that checks if the selected colors match the row’s color.

Filtered on Colour = 
VAR IsAllSelected = 
    ISFILTERED('Color Category'[ColorName]) = FALSE

VAR RowColorHex = [Sales Amount Color]

VAR SelectedColorsHex = 
    CONCATENATEX(
        VALUES('Color Category'[HexCode]),
        'Color Category'[HexCode],
        ","
    )

RETURN
    IF (
        IsAllSelected || CONTAINSSTRING(SelectedColorsHex, RowColorHex),
        1,
        BLANK()
    )

• 'VAR SelectedColorsHex' variable generates a comma-separated list of all the selected hex color codes from the Color Category[HexCode] column.

• VALUES returns a unique list of hex codes that are currently in context (e.g., selected in a slicer).

• CONCATENATEX then concatenates these hex codes into a single string with commas as separators.

This measure will return 1 for rows where the color matches the selected colors in the slicer, and BLANK() otherwise.(This code handle scenarios of All selections+Multi selections as well)

Step 4: Applying the Filter to Your Visual

Add the 'Filtered on Colour' measure as a visual level filter.

Step 5: Setting Up the Slicer

Use the Color Name column from the Color Category table as your slicer. This slicer will now allow users to filter the table based on the colors.

Step 6: Testing and Validating

Ensure your slicer interacts correctly with your visual, displaying only the rows that match the selected color codes.

Conclusion:

With this approach, you can easily filter conditional formats based on colors without breaking any existing relationships or creating unnecessary complexity. This technique enhances user interaction and makes it easier to gain insights based on visually appealing formats.