Forum Discussion
Filtering within a VALUES function based on another field
Hi all - hoping someone can help me with this (my first plea for help!)
I'll give an anonymised example of what I need to do - the real version is obviously a bit more complicated!
I have a table with multiple fields. Two of those fields are named Fruit and Colour, looking something like this:
Fruit Colour
Avocado green
Lemon yellow
Grapes red
Grapes green
Grapes black
Apple green
I want to use the VALUES function to return the names of the fruit where the colour is green. This is then being used within a CROSSJOIN as part of a larger statement for a mapping table, for example:
Slicer =
var fruit = CROSSJOIN(ROW("Type","Fruit"), VALUES('Table'[fruit] *but only where fruit is green*))
var vegetable = CROSSJOIN(ROW("Type","Vegetable"), VALUES('Table'[Vegetable]))
return UNION(fruit,vegetable)
This works to return all fruit and vegetables, but can anyone please tell me how to filter the list of fruit down?
Thanks!
Another approach is to use CALCULATETABLE(VALUES(Table[Fruit]), Table[Colour] = "green").
If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
7 Replies
Another approach is to use CALCULATETABLE(VALUES(Table[Fruit]), Table[Colour] = "green").
If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
Thank you very much for this mahoneypat ! It's a neat solution, and I feel it's easy for another user to read the logic 🙂
This answer is great for me too, thanks! I am calculating Average Weekly Sales per year and I couldn't manage to filter my column yearweek (containing Y2021) from the VAR that had VALUES function 🙂
Thanks az38 , this is perfect and works. I can use this within my query without actually having to create a table to give me:
Slicer =
var fruit = CROSSJOIN(ROW("Type","Fruit"), SELECTCOLUMNS(FILTER('Table','Table'[Colour]="Green"),"Fruit",'Table'[Fruit]))
var vegetable = CROSSJOIN(ROW("Type","Vegetable"), VALUES('Table'[Vegetable]))
return UNION(fruit,vegetable)
s45kougo Not sure I understand the scenario here, but my first thought was this:
Slicer =
var fruit = CROSSJOIN(ROW("Type","Fruit"), filter(VALUES('Table'[fruit]),'Table'[Colour]="Green"))
var vegetable = CROSSJOIN(ROW("Type","Vegetable"), VALUES('Table'[Vegetable]))
return UNION(fruit,vegetable)
