Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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.
Solved! Go to Solution.
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
Proud to be a Super User!
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
Proud to be a Super User!
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.
Thank you so much!!!
Check out the July 2025 Power BI update to learn about new features.
User | Count |
---|---|
72 | |
70 | |
38 | |
27 | |
26 |
User | Count |
---|---|
97 | |
96 | |
59 | |
44 | |
40 |