Error in Switch Function

[Anonymous]

Hi, 

I am trying to use the following measure that I wrote but I am getting an error:

VAR selectedbrands = 
SWITCH(
INTERSECT(VALUES(Brands[Brand]), VALUES('Brands + Others'[Brand])),
"Brand1 combined", INTERSECT(VALUES('Brands + Others'[Brand]),  VALUES(Brand1[brand])),
"Brand2 combined", INTERSECT(VALUES('Brands + Others'[Brand]),  VALUES(Brand2[brand])),
"Brand3 combined", INTERSECT(VALUES('Brands + Others'[Brand]),  VALUES(Brand3[brand])),
INTERSECT(VALUES(Brands[Brand]), VALUES('Brands + Others'[Brand]))
 )
RETURN selectedbrands

This is the error:
The True/False expression does not specify a column. Each True/False expressions used as a table filter expression must refer to exactly one column.

Any suggestions?

Thanks.

[v-kkf-msft]

Hi @Anonymous ,

Has your problem been solved? If it is solved, please mark a reply which is helpful to you.

If the problem is still not resolved, please share some sample data and expected output, which will help us to solve the problem as soon as possible.

Best Regards,
Winniz

[PaulDBrown]

INTERSECT returns a table of values. SWITCH requires a single reference as an input (Row or Filter context of a table, measure or string); the ouput must also be a scalar value.

what are you trying to calculate?

[Anonymous]

Hi PaulDBrown, 

Thanks for your reply, I realized that SWITCH will not work so I used nested IF statements(check the latest code down below).

[PaulDBrown · Accepted solution]

You still have the problem with the IF(INTERSECT()... 
You need an expression with which the IF or SWITCH can establish a valid reference. Something along the lines of 

SWICTH (SELECTEDVALUE(Table[Column]),....

or
IF(SELECTEDVALUE(Table[Column]),...

Or

SWICTH(TRUE(),
[Measure[ <=  1000, ....

If you are using SWICTH or IF in a calculated column, you can leave out the SELECTEDVALUE type expressions.... 

In your case you need to specify the scalar output (you cannot use a table expression such as INTERSECT) along the lines of:

Measure1 =
VAR _Table =
    INTERSECT ( VALUES ( Brands[Brand] ), VALUES ( 'Brands + Others'[Brand] ) )
RETURN
    SWITCH (
        TRUE (),
        "Brand1 combined" IN _Table, "Output1",
        "Brand2 combined" IN _Table, "Output2",
        "Brand3 combined" IN _Table, "Output3",
        "..."
    )

Bear in mind that the value is calculated in order of the expressions. So if the first experssion is true, the measure returns Output1, if it return false, it moves onto the next expression and so on...

[Dhacd]

Hi @Anonymous 
I believe you are using the switch statement in the wrong way.

SWITCH (
    [A],
    0, "Zero",
    1, "One",
    2, "Two",
    "Other numbers"
)

For the above code, the value for A under a particular row is checked whether 0 or 1 or 2, and the corresponding value is returned.
Please cross-check whether you are following the same syntax.

If this post helps, then please consider accepting it as the solution to help the other members find it more quickly.
Regards,
atma.

[Anonymous]

I think the expression in SWITCH() should return a scalar value so I wrote the following code in place of the previous one:

VAR selectedbrands = 
IF(INTERSECT(VALUES(Brands[Brand]), VALUES('Brands + Others'[Brand]))="Brand 1 Combined",
  INTERSECT(VALUES('Brands + Others'[Brand]), VALUES(Brand1[brand])),
  IF(INTERSECT(VALUES(Brands[Brand]), VALUES('Brands + Others'[Brand]))="Brand 2 Combined", 
    INTERSECT(VALUES('Brands + Others'[Brand]), VALUES(Brand2[brand])) ,
    IF(INTERSECT(VALUES(Brands[Brand]), VALUES('Brands + Others'[Brand]))="Brand 3 Combined", 
      INTERSECT(VALUES('Brands + Others'[Brand]), VALUES(Brand3[brand])),
      INTERSECT(VALUES(Brands[Brand]), VALUES('Brands + Others'[Brand]))
      )
     )
   )

But I am still getting this error:

The True/False expression does not specify a column. Each True/False expressions used as a table filter expression must refer to exactly one column.

[Dhacd]

@Anonymous the syntax is again not correct. 
It is like below.
Single if
If( "condition expression", value to be returned if true, value if returned it is false)

Nested if
If( "condition expression", value to be returned if true,

      If( "condition expression",value to be returned if true,
              If( "condition expression", value to be returned if true,
                    If( "condition expression", value to be returned if true,Default value))))

Please cross-check whether you are following correctly.

If this post helps, then please consider accepting it as the solution to help the other members find it more quickly.

Regards,
Atma.