Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
acg
Resolver I
Resolver I

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?

 

 

1 ACCEPTED SOLUTION
acg
Resolver I
Resolver I

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

View solution in original post

3 REPLIES 3
acg
Resolver I
Resolver I

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
Super User
Super User

@acg ,

Why do you have 'default' value below?

ERD_0-1683196342758.png

From what tables do you take data for this visual?

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your Kudos.

Check out my latest demo report in the data story gallery.

Stand with Ukraine!


Here are official ways you can support Ukraine financially (accounts with multiple currencies):
1) Support the Armed Forces of Ukraine: https://bank.gov.ua/ua/about/support-the-armed-forces
2) Come Back Alive foundation: https://www.comebackalive.in.ua/

Thank you!

@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. 

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors