Forum Discussion
Joining and Filtering
Let's say I have three tables:
Food Table (Coming from OData service):
| ID | Food |
| 1 | Apple |
| 2 | Orange |
| 3 | Lettuce |
| 4 | Carrot |
| 5 | Grape |
I also have two reference tables:
Fruits
| ID |
| 1 |
| 2 |
| 5 |
Vegetables
| ID |
| 3 |
| 4 |
Problem/Goal:
On my report page, I have a table of all the food. I'd like to create a slicer that allows me to toggle showing only fruit or vegetables or both. This is a contrived set of data, but the real data set may have items that fall into both subcategories (e.g. with a table with colors, if one chose "orange," then both orange and carrot would be visible).
I could fill my table with bits such as:
| ID | Food | Fruit | Vegetable | Orange | Red | Green |
| 1 | Apple | 1 | 1 | |||
| 2 | Orange | 1 | 1 | |||
| 3 | Lettuce | 1 | 1 | |||
| 4 | Carrot | 1 | 1 | |||
| 5 | Grape | 1 | 1 |
So, how do I create a "Slicer" with various options such as, fruit, vegetable, orange, red, and green to filter? Or, am I going about this the wrong way?
Thanks!
Hi a11smiles ,
1 Create a table for slicer contains fruit,vegetable,orange, red and green.
2 Create a matrix table then ID, Food in the rows and options in the columns.
3 Create a measure like this:
Measure = VAR _1 = SELECTEDVALUE ( 'Food Table'[ID] ) RETURN SWITCH ( SELECTEDVALUE ( Slicer[options] ), "fruit", IF ( _1 IN VALUES ( Fruits[ID] ), 1 ), "vegetable", IF ( _1 IN VALUES ( Vegetables[ID] ), 1 ), "red", IF ( _1 IN VALUES ( Red[ID] ), 1 ), "orange", IF ( _1 IN VALUES ( Orange[ID] ), 1 ), "green", IF ( _1 IN VALUES ( Green[ID] ), 1 ) )Result:
Pbix in the end you can refer.
Best Regards
Community Support Team _ chenwu zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
1 Reply
Hi a11smiles ,
1 Create a table for slicer contains fruit,vegetable,orange, red and green.
2 Create a matrix table then ID, Food in the rows and options in the columns.
3 Create a measure like this:
Measure = VAR _1 = SELECTEDVALUE ( 'Food Table'[ID] ) RETURN SWITCH ( SELECTEDVALUE ( Slicer[options] ), "fruit", IF ( _1 IN VALUES ( Fruits[ID] ), 1 ), "vegetable", IF ( _1 IN VALUES ( Vegetables[ID] ), 1 ), "red", IF ( _1 IN VALUES ( Red[ID] ), 1 ), "orange", IF ( _1 IN VALUES ( Orange[ID] ), 1 ), "green", IF ( _1 IN VALUES ( Green[ID] ), 1 ) )Result:
Pbix in the end you can refer.
Best Regards
Community Support Team _ chenwu zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.