Forum Discussion
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.
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.
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
3 Replies
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.
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