PowerBI variable selection from union tables

Question

I have two tables, table1 & table2. I created 'Union' of these two tables in PowerBI.

table1
id   city   fruit         table
a1   NY     apple         table1
a2   NY     apple         table1
a3   NY     pear          table1
a4   MI     pear          table1
a5   MI     apple         table1
a6   MI     strawberry    table1
a7   TO     strawberry    table1
a8   TO     strawberry    table1
a9   TO     kiwi          table1

table2
id   city   fruit         table
a11  NY     rockmelon     table2
a12  NY     rockmelon     table2
a13  BJ     pear          table2
a14  BJ     pear          table2
a15  BJ     apple         table2
a16  LA     blueberry     table2
a17  LA     blueberry     table2
a18  PA     blueberry     table2
a19  PA     kiwi          table2

I would like to create a filter "filter1", which takes all rows if fulfilling these criteria: "city" in table1 is "MI", or "fruit" in table2 is "blueberry". If the criteria is met, call it "Awesome" in the dropdown of "filter1".

Can you please see if the following measure is in the right direction, and how to proceed? Thank you.

filter1 = 
VAR
  table1 = FILTER('Union', 'Union'[table]="table1" && 'Union'[city]="MI")
VAR
  table2 = FILTER('Union', 'Union'[table]="table2" && 'Union'[fruit]="blueberry")

Almond Almond · Accepted Answer · 2021-04-07T06:29:07

Awesome_test =

var filter1 = FILTER(union, union[city]= "MI" && union[table]="table1")

var filter2 = FILTER(union, union[fruit]= "blueberry" && union[table]="table2")

var filter3 = UNION(filter1, filter2)

RETURN CALCULATE(MAX(union[id]), filter3)

// then pull the new measure into filter, select all that contains "a", then only those that fulfill the requirements are retained.

PowerBI variable selection from union tables

1 Answers