Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello all,
I was wondering if you could help me out. I have my visuals (table) where I need to drill down by two slicers which are from two different data sources.
I am hoping to have only one slicer with following 5 conditons as it is quite difficult to use two slicer with given condtions.
Is there any smart way how to achive that?
Many thanks.
Kind regards.
Andrej
Solved! Go to Solution.
Create a table by entering data, don't create any relationship for this table
category |
category1 |
category2 |
category3 |
category4 |
category5 |
Create columns in main table
1 = IF ( [category] IN { "Train" } && FIND ( [Number], FORMAT ( CONCATENATEX ( VALUES ( 'main table'[Number] ), [Number], "," ), "General Number" ), 1, 0 ) > 0, "category1" ) 4 = IF ( [category] IN { "Car", "Airplane", "Train" } && FIND ( [Number], FORMAT ( CONCATENATEX ( VALUES ( 'main table'[Number] ), [Number], "," ), "General Number" ), 1, 0 ) > 0, "category4" ) 5 = IF ( [category] IN { "Car", "Airplane", "Train", "Boat" } && FIND ( [Number], FORMAT ( CONCATENATEX ( VALUES ( 'main table'[Number] ), [Number], "," ), "General Number" ), 1, 0 ) > 0, "category5" ) 3 = IF ( [category] IN { "Car", "Airplane", "Train" } && FIND ( [Number], CONCATENATEX ( FILTER ( VALUES ( 'main table'[Number] ), NOT [Number] IN { 5649 } ), [Number], "," ), 1, 0 ) > 0, "category3" ) 2 = IF ( [category] IN { "Car", "Airplane", "Train" } && FIND ( [Number], CONCATENATEX ( FILTER ( VALUES ( 'main table'[Number] ), NOT [Number] IN { 6546, 5649 } ), [Number], "," ), 1, 0 ) > 0, "category2" )
Then
Add "category" in a slicer
create a measure in main table
Measure = SWITCH ( SELECTEDVALUE ( 'slicer table'[category] ), "category1", IF ( MAX ( 'main table'[1] ) = "category1", 1 ), "category2", IF ( MAX ( 'main table'[2] ) = "category2", 1 ), "category3", IF ( MAX ( 'main table'[3] ) = "category3", 1 ), "category4", IF ( MAX ( 'main table'[4] ) = "category4", 1 ), "category5", IF ( MAX ( 'main table'[5] ) = "category5", 1 ) )
Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
You could use filter pane to filter the "Number" in excel
Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hello @v-juanli-msft
Thanks for your help.
I am aware of filters but that is not what I need.
I have table with lots of variables. This table is linked via relationships to two separate data sources Excel 1 & Excel 2 which works as slicers . At the moment I can work with my table with two separate Slicers : Category & Number
I need to create 1 slicer which will meet my conditions for Category 1-5.
Hope that make sense.
Here is picture with further explanation.
Many thanks.
Kind regards.
Andrej
Create a table by entering data, don't create any relationship for this table
category |
category1 |
category2 |
category3 |
category4 |
category5 |
Create columns in main table
1 = IF ( [category] IN { "Train" } && FIND ( [Number], FORMAT ( CONCATENATEX ( VALUES ( 'main table'[Number] ), [Number], "," ), "General Number" ), 1, 0 ) > 0, "category1" ) 4 = IF ( [category] IN { "Car", "Airplane", "Train" } && FIND ( [Number], FORMAT ( CONCATENATEX ( VALUES ( 'main table'[Number] ), [Number], "," ), "General Number" ), 1, 0 ) > 0, "category4" ) 5 = IF ( [category] IN { "Car", "Airplane", "Train", "Boat" } && FIND ( [Number], FORMAT ( CONCATENATEX ( VALUES ( 'main table'[Number] ), [Number], "," ), "General Number" ), 1, 0 ) > 0, "category5" ) 3 = IF ( [category] IN { "Car", "Airplane", "Train" } && FIND ( [Number], CONCATENATEX ( FILTER ( VALUES ( 'main table'[Number] ), NOT [Number] IN { 5649 } ), [Number], "," ), 1, 0 ) > 0, "category3" ) 2 = IF ( [category] IN { "Car", "Airplane", "Train" } && FIND ( [Number], CONCATENATEX ( FILTER ( VALUES ( 'main table'[Number] ), NOT [Number] IN { 6546, 5649 } ), [Number], "," ), 1, 0 ) > 0, "category2" )
Then
Add "category" in a slicer
create a measure in main table
Measure = SWITCH ( SELECTEDVALUE ( 'slicer table'[category] ), "category1", IF ( MAX ( 'main table'[1] ) = "category1", 1 ), "category2", IF ( MAX ( 'main table'[2] ) = "category2", 1 ), "category3", IF ( MAX ( 'main table'[3] ) = "category3", 1 ), "category4", IF ( MAX ( 'main table'[4] ) = "category4", 1 ), "category5", IF ( MAX ( 'main table'[5] ) = "category5", 1 ) )
Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.