Filtering within a VALUES function based on another field

[s45kougo]

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!

[mahoneypat · Accepted solution]

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

[abeldiazcastano]

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 🙂 

[s45kougo]

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 🙂

[DataZoe]

@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)

[s45kougo]

@DataZoe I'd hoped that this would work as I was thinking along the same lines.  However, because VALUES returns a single column, [Colour] cannot be the second parameter in the FILTER function.  Many thanks for replying anyway 🙂

[az38]

Hi @s45kougo 

try a table

Table 2 = SELECTCOLUMNS(FILTER('Table', 'Table'[Colour]="green"), "Fruit", 'Table'[Fruit])

[s45kougo]

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)