Cross-filter on a related table

[MightyMicrobe]

I have two tables: one with all customer IDs and their attributes and the other with a subset of these IDs and some other attributes: 

Table 1		Table 2
ID	(expected result)	ID	Foo	Spam
101	Both	101	1	1
102	None	104	0	1
103	None	107	0	1
104	Spam	109	1	0
105	None	110	1	1
106	None	108	0	1
107	Spam
108	Spam
109	Foo
110	Both
111	None
112	None

The tables are linked by IDs, which works well: I can create filtered calculations like DISTINCTCOUNT('Table1'[ID], FILTER('Table1', OR([Foo]=1, [Spam]=1)... so far so good. 

What I'm trying to create, however, is a SWITCH or IF measure in Table 1 which would put a flag against each ID in Table 1 based on the values of [Foo] and [Spam] in Table 2 AND put 'None' if the ID is not found in Table 2 altogether. 

Something like

IF 'Table2'[Foo]=1 THEN 'Foo',

ELSEIF 'Table2'[Spam]=1 THEN 'Spam',

ELSEIF AND('Table2'[Foo]=1, 'Table2'[Spam]=1) THEN 'Both'

ELSE 'None'

But all I can see from Table 2 when trying to build the SWITCH is the aggregate metrics, not the [Foo] and [Spam] columns.

What gives?

[ryan_mayu · Accepted solution]

@MightyMicrobe 

Please try this

RESULT = 
VAR FOO=MAXX(FILTER(Table2,Table1[ID]=Table2[ID]),Table2[FOO])
VAR SPAM=MAXX(FILTER(Table2,Table1[ID]=Table2[ID]),Table2[SPAM])
RETURN IF(FOO=1 && SPAM=1 , "BOTH",IF(FOO=1 &&SPAM=0,"FOO",if(FOO=0&&SPAM=1,"SPAM","NONE")))

[amitchandak]

@MightyMicrobe , Not sure I got it , Try

New column in Table 1 = maxx(filter(table2, table2[ID] = Table1[ID]), Switch(True(), 'Table2'[Foo]=1 , "Foo",'Table2'[Spam]=1 , "Spam",
AND('Table2'[Foo]=1, 'Table2'[Spam]=1) , "Both",
"None"
))

[MightyMicrobe]

Hey @amitchandak thank you for your help. My main problem was that the relationship was single-directional, that's why I could not see the fields. 

I modified your formula so that 'Both' shows up first, otherwise, it did not show up because every ID only evaluated once. 

However, I do not get the 'None' in the results, only blanks -- any ideas?

Column =

MAXX(

FILTER('Table2', 'Table2'[ID]='Table1'[ID]),

SWITCH(TRUE(),

AND('Table2'[Spam]=1, 'Table2'[Foo]=1),"Both",

'Table2'[Spam]=1,"Spam",

'Table2'[Foo]=1,"Foo",

"None"

))