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.
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!
Solved! Go to 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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
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
To learn more about Power BI, follow me on Twitter or subscribe 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 🙂
@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 🙂
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)
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 |