Forum Discussion

s45kougo's avatar
s45kougo
Helper I
6 years ago
Solved

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 tabl...
Need Help
  • mahoneypat's avatar
    mahoneypat
    6 years ago

    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

az38's avatar
az38
Community Champion
6 years ago

Hi s45kougo 

try a table

Table 2 = SELECTCOLUMNS(FILTER('Table', 'Table'[Colour]="green"), "Fruit", 'Table'[Fruit])
s45kougo's avatar
s45kougo
Helper I
6 years ago

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)