topic Re: Conditional statements across different tables in DAX Commands and Tips

Conditional statements across different tables

acg — Thu, 04 May 2023 11:07:49 GMT

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?

Re: Conditional statements across different tables

ERD — Thu, 04 May 2023 10:32:57 GMT

acg ,

Why do you have 'default' value below?

From what tables do you take data for this visual?

Re: Conditional statements across different tables

acg — Thu, 04 May 2023 20:02:34 GMT

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.

Re: Conditional statements across different tables

acg — Fri, 05 May 2023 02:28:00 GMT

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