Conditional statements across different tables

[acg]

I have two tables looking at schemes and their activity levels. I need to create a new variable (Retail / Default) that takes different conditional scenarios across these two tables into account. 

Table1: 

Question Library

Scheme ID	Active/Default
1	active
2	active
3	default
4	active
5	default

Table 2: 

Schemes

Scheme ID	Is Name Restricted
1	yes
2	no
3	yes
4	NULL
5	yes
6	no

Table 1 with new variable

Scheme ID	Active/Default	Retail / Default
1	active	restricted
2	active	retail
3	default	restricted
4	active	retail
5	default	restricted
6	default	default

The condition I need is:

Any scheme that is Restricted is labelled 'Restricted"' in the new variable: Retail / Default 

Default values in table 1 that have not been overwritten by a "restricted" label from Tabel 2 remain  as 'default'. 

 All other schemes that are not default (Table 1) and not restricted (Table 2) are 'retail'.

I found a similar question, but I cannot replicate it.

A lookup is built first

I tried this, but it does not like the last 'Scheme'[Scheme ID] , but I don’t understand what to put in the in its place. My understanding was that it needs something to connect to between the two tables:

Combined Table = LOOKUPVALUE('Question Library'[Active/Default], 'Question Library KSAR'[Scheme ID], 'Scheme'[Scheme ID])

 In the second part the conditions are laid out, and unable to get the lookupvalue right, I tried to use a userelationship

Retail / Default =

SWITCH (TRUE(),

'Question Library'[Active/Default ]="Default", "Default",

(USERELATIONSHIP('Scheme'[Restricted Scheme Name] = "Yes"), "Restricted",

'Question Library'[Active/Default]="Active" &&'Scheme'[Restricted Scheme Name]="No","Retail"

)

This is the link I used:

https://community.powerbi.com/t5/Desktop/IF-statements-with-conditions-in-another-table/m-p/2992307

Can someone get me on the right way?

[acg · Accepted solution]

After some discussion in the team, this is the solution:

Scheme type  = 
VAR ActiveDefault = 'Question Library KSAR'[Active/Default]
VAR RestrictedStatus = RELATED('Scheme'[Restricted Scheme Name])
RETURN

SWITCH(
    TRUE(),
    ActiveDefault = "Active" && RestrictedStatus = "Yes", "Restricted",
    ActiveDefault = "Active" && RestrictedStatus = "No", "Retail",
    ActiveDefault = "Default" && RestrictedStatus = "Yes", "Restricted",
    ActiveDefault = "Default" && RestrictedStatus = "No", "Default",
    "Retail"
)

[ERD]

@acg ,

Why do you have 'default' value below?

From what tables do you take data for this visual?

[acg]

@ERD  - I have added more explanation. What do you mean with: From what tables do you take data for this visual?

Essentially SQL imports. Table 1 and Table 2 are connected via Scheme ID.  The Retail\Default variable is that new variable that needs to be built based on the conditions explained above.