Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

Slicer with multiple fields without unpivoting

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.

2 ACCEPTED SOLUTIONS
Icey
Community Support
Community Support

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"
                            )
                        )
                    )
                )
            )
        )
    )
)

slicer column.PNG

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.

slicer custom.PNGSlicer with multiple fields without unpivoting 2.PNG

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

slicer this visual.PNGslicer this page.PNG

 

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
Solution Sage
Solution Sage

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!




View solution in original post

3 REPLIES 3
AnthonyTilley
Solution Sage
Solution Sage

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!




Icey
Community Support
Community Support

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"
                            )
                        )
                    )
                )
            )
        )
    )
)

slicer column.PNG

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.

slicer custom.PNGSlicer with multiple fields without unpivoting 2.PNG

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

slicer this visual.PNGslicer this page.PNG

 

Best Regards,

Icey

 

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

Anonymous
Not applicable

Thank you so much!!!

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.