Forum Discussion

acg's avatar
acg
Resolver I
3 years ago
Solved

Conditional statements across different tables

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?

 

 

DAX
  • acg's avatar
    acg
    3 years ago

    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"
)

3 Replies

  • ERD's avatar
    ERD
    Community Champion
    3 years ago

    acg ,

    Why do you have 'default' value below?

    From what tables do you take data for this visual?

     

    • acg's avatar
      acg
      Resolver I
      3 years ago

      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. 

  • acg's avatar
    acg
    Resolver I
    3 years ago

    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"
)