Solved: Re: Slicer with multiple fields without unpivoting

Anonymous · ‎09-05-2019

Does anyone know if it is possible to create a slicer containing multiple fields without unpivoting the data. I would want to create a slicer containing Red=1, Blue=1 and Orange=1. Here is an example of my data:

ID	Time	Red	Blue	Orange
1	0	1	0	1
1	1	1	0	1
1	2	1	1	1
2	0	1	1	0
2	1	0	0	0
2	2	1	1	1
3	0	1	1	0
3	1	0	1	1
3	2	0	1	1
4	0	1	0	1
4	1	0	0	1
4	2	1	1	0
5	0	1	0	1
5	1	0	0	1
5	2	0	1	1
6	0	1	1	0
6	1	0	0	0
6	2	1	0	0
7	0	1	1	0
7	1	1	1	1
7	2	1	1	1
8	0	1	1	0
8	1	0	0	1
8	2	0	0	1
9	0	1	1	0
9	1	1	1	1
9	2	0	0	1
10	0	1	1	0
10	1	1	1	0
10	2	1	0	0

I don't want to unpivot because there are already multiple copies of an ID for each time point.

Icey · ‎09-06-2019

Hi @Anonymous ,

There are some workarounds.

1. You can create a column. But this does not apply to a large number of colors.

Slicer =
IF (
    'Table'[Red] = 1
        && 'Table'[Blue] = 1
        && 'Table'[Orange] = 1,
    "Red=1,Blue=1,Orange=1",
    IF (
        'Table'[Red] = 1
            && 'Table'[Blue] = 1
            && 'Table'[Orange] = 0,
        "Red=1,Blue=1,Orange=0",
        IF (
            'Table'[Red] = 1
                && 'Table'[Blue] = 0
                && 'Table'[Orange] = 1,
            "Red=1,Blue=0,Orange=1",
            IF (
                'Table'[Red] = 1
                    && 'Table'[Blue] = 0
                    && 'Table'[Orange] = 0,
                "Red=1,Blue=0,Orange=0",
                IF (
                    'Table'[Red] = 0
                        && 'Table'[Blue] = 1
                        && 'Table'[Orange] = 1,
                    "Red=0,Blue=1,Orange=1",
                    IF (
                        'Table'[Red] = 0
                            && 'Table'[Blue] = 1
                            && 'Table'[Orange] = 0,
                        "Red=0,Blue=1,Orange=0",
                        IF (
                            'Table'[Red] = 0
                                && 'Table'[Blue] = 0
                                && 'Table'[Orange] = 1,
                            "Red=0,Blue=0,Orange=1",
                            IF (
                                'Table'[Red] = 0
                                    && 'Table'[Blue] = 0
                                    && 'Table'[Orange] = 0,
                                "Red=0,Blue=0,Orange=0"
                            )
                        )
                    )
                )
            )
        )
    )
)

2. You can try to use a custom visual – Power Slicer. But pay attention to ‘0’ which doesn’t display. And the color order is related to where you are placed.

3. You can use Filters on this visual, or Filters on this page, or Filters on this report.

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

AnthonyTilley · ‎09-06-2019

unfortunatly what your asking for creates a parodox

you can createa colunm for IF([red] = 1, "RED",BLANK())

and you can do this across the three colunms IF([red] = 1, "RED",IF([BLUE] = 1, "BLUE",IF([ORANGE] = 1, "ORANGE",BLANK()))))

the issue is there is no way to say that a value is both RED and BLUE

you could have several extra options in your slicer for EXAMPLE

RED

BLUE

ORANGE

RED - BLUE

RED - ORANGE

BLUE - ORANGE

RED - BLUE - ORANGE

you could achive this by comparing all three values in nested if statments and then use a text filter to say contains red, or contains blue

but there is no option without unpivioting to create a slicer for just red, blue and orange

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!