Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!!!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
115 | |
100 | |
88 | |
68 | |
61 |
User | Count |
---|---|
150 | |
120 | |
100 | |
87 | |
68 |