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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.