Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
s45kougo
Helper I
Helper I

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!

1 ACCEPTED SOLUTION
mahoneypat
Microsoft Employee
Microsoft Employee

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





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

6 REPLIES 6
mahoneypat
Microsoft Employee
Microsoft Employee

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





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


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
Microsoft Employee
Microsoft Employee

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

Respectfully,
Zoe Douglas (DataZoe)



Follow me on LinkedIn at https://www.linkedin.com/in/zoedouglas-data
See my reports and blog at https://www.datazoepowerbi.com/

@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
Community Champion
Community Champion

Hi @s45kougo 

try a table

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

do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn

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)

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.