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.
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:
Solved! Go to 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)
@Serdet , 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]))
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".
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)
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 |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |