Value Function Filter by Text

[Anonymous]

I have the following datasets:

Dataset 1 

Time Delta (Minutes)	Child Category
4	Pear
6	Grape
7	Carrot
8	Potato

Dataset 2 

Parent Category

Fruit

Vegetable

I have dataset 1 displayed in a table and have  put dataset 2 into a slicer. When fruit or vegetable is in the slicer I need relevant data to be shown in the table. 

I have created the following measure:

Category Selection =

SWITCH(

TRUE(),

"Fruit" IN ALLSELECTED(Dataset2[ParentCategory]), VALUES(Dataset1[ChildCategory]),

"Vegetable" IN ALLSELECTED(Dataset2[ParentCategory]),VALUES(Dataset1[ChildCategory])

)

Please can someone help me out with the in the above DAX to achieve the FILTER required? Fruit selection will need to show 'Pear' & 'Grape', Vegetable selection will need to show 'Carrot' 'Potato'.

Many thanks,

[amitchandak]

@Anonymous , Ideally Second table should have distinct categories and parent for that. That I do not see. In that case you can join two tables on category and filter will automatically pass

Assuming second has distinct categories and parent and not joined

plot this measure with category of table 1

countrows(filter(Table1, Table1[Category] in values(Table1[Category]))

[Anonymous]

Apologies as I am not that familiar with Dax. I have tried the above and it doesn't seem to be working.

I would have thought when Fruit is selected in the slicer it would have to filter out dataset 1 based upon text values "Pear" and "Grape".

[V-lianl-msft · Accepted solution]

You need to create a bridge table or column. According to your definition, grouping Child categories into parent categories. Only relying on DAX to solve and ignore the wrong model is not the best solution.

You can try to create a calculated column and create a relationship with the parent category in another table.

Parent Cateory = 
 SWITCH(TRUE(),
    'Dataset 1'[Child Category] in {"Pear","Grape"},"Fruit",
    'Dataset 1'[Child Category] in {"Carrot","Potato"},"Vegetable")

If you want to use measure, please create the following measure and apply it to the visual level filter:

Measure = 
var Parent_Cateory = 
 SWITCH(TRUE(),
    MAX('Dataset 1'[Child Category]) in {"Pear","Grape"},"Fruit",
    MAX('Dataset 1'[Child Category]) in {"Carrot","Potato"},"Vegetable")
return IF(Parent_Cateory in VALUES('Table'[Parent Category ]),1,0)