Dropdown from multiple tables by conditional SWITCH()

Hello… I need help to get column data from different table using Can I select columns from different table using SWITCH()?

I have two dropdown - a) Brand and b) Category.
Brand is an enum dropdown with base type ‘text’. Category is an enumlist dropdown with base type ‘text’. The column data i want is also type ‘text’.

Each brand has multiple product categories. However, all the categories for each brand are in different tables having same column structure. Depending on the brand selected, the category dropdown should change… I am trying the below expression under Valid_if constraint:

SWITCH( TEXT([Principal]),
“Brand A”,SELECT(Table A[Category], true),
“Brand B”,SELECT(Table B[Category],true),
SELECT(Table A[Category],true)
)

But i keep getting this error:
“SWITCH function is used incorrectly:Inputs to SWITCH() must be an initial expression, one or more value-result pairs, and a default result. Input 5 is not a value of a consistent type.”

Kindly help.
Thank you.

1 Like

@Nirmal_Giri
Rather than SWITCH, I advise using IFS() instead:

IFS(
    “Brand A” = TEXT([Principal]), SELECT(Table A[Category], true),
    “Brand B” = TEXT([Principal]), SELECT(Table B[Category],true),
    TRUE, SELECT(Table A[Category],true)
)
1 Like

@LeventK Thank you for replying.

I tried the above and get an error message:
“IFS function is used incorrectly:Inputs to IFS() must be condition-value pairs. Input 4 is not a value of a consistent type.”

The expression is:
IFS(
“Scavini”=TEXT([Principal]),SELECT(Scavini_Category[Equipment],TRUE),
“PCS Instruments”=TEXT([Principal]),SELECT(PCS_Category[Equipment],TRUE),
TRUE,SELECT(Scavini_Category[Equipment],TRUE)
)

The error indicates that the data Type of your column is inconsistent with the data this expression is evaluating to. Your expression evals to a List. What’s your column’s data type and what data do you expect it to return?

1 Like

@LeventK
The [Equipment Category] is an enumlist with base type as text. I want to display list of categories for the [Principal] selected.
columns

Just to know, I tried an simple IF expression.
IF([Principal]=“Scavini”,Scavini_Category[Equipment],PCS_Category[Equipment])

This worked and I got my list of categories under [Equipment Category] when I select different Principal.
But an IFS expression fails. I have multiple principals hence a simple IF expression wont work.

So now I can be certain that my data types and column type is matching and consistent as it works for simple IF expression.

You need to use nested IF() statements for this purpose like…
IF([Brand]=“Brand A”,TableA[Category],
IF([Brand]=“Brand B”,TableB[Category],
TableC[Category]))

2 Likes

@Aleksi It worked. Thank you.

Can you explain why IFS or SWITCH didn’t work.
The expression you provided is same as IFS. Then why does a nested IF work but not IFS.

SWITCH() and IFS() doesn’t accept reading from two different tables even your tables have the same structure.

1 Like

Try:

SWITCH(
  [Principal],
  "Brand A",
    (LIST() + Table A[Category]),
  "Brand B",
    (LIST() + Table B[Category]),
  (LIST() + Table A[Category])
)
3 Likes

@Steve This one also works. Thank you.

Additional question, I have a table ‘Quotation’ that consists of 10 columns and each column has an app formula that performs conditional LOOKUP or SELECT to get matching record depending on [Principal] selected. An example of app formula used to get the [Equipment Name]:

Example App Formula

SWITCH(
[Principal],
“Brand A”, (SELECT(Table A[Equipment],[_THISROW].[Part_Code_Desc]=[Part_Code_Desc])),
“Brand B”, (SELECT(Table B[Part_Code_Desc],[_THISROW].[Part_Code_Desc]=[Part_Code_Desc])),
“Brand C”, (SELECT(Table C[Equipment],[_THISROW].[Part_Code_Desc]=[Part_Code_Desc])),
(SELECT(Table D[Equipment],[_THISROW].[Part_Code_Desc]=[Part_Code_Desc]))
)

I am using similar expression for remaining columns. Each of the above table contains approx. 400 records.

  1. Which conditional expression would be better suited in terms of performance - SWITCH or nested IF?
  2. Similarly, which would be more appropriate - SELECT() or LOOKUP()

For the example you provided, SWITCH() is more efficient.

LOOKUP() should be considered if you’re sure there will only be one result to return, or you only care to get one from several possible results. SELECT() should be considered if there might be more than one result, and you want more than only the first one. Under the hood, LOOKUP() is exactly equivalent to ANY(SELECT()), so it’s really a matter of what results you expect and personal preference.

3 Likes