Joining and Filtering

[a11smiles]

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!

[v-chenwuz-msft · Accepted solution]

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.