The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event: Join us in Stockholm, September 24-27, 2024.
Save €200 with code MSCUST on top of early bird pricing!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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?
Solved! Go to 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"
)
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"
)
@acg ,
Why do you have 'default' value below?
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.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the August 2024 Power BI update to learn about new features.
User | Count |
---|---|
18 | |
18 | |
17 | |
15 | |
11 |
User | Count |
---|---|
35 | |
34 | |
19 | |
19 | |
14 |