Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Error in Switch Function

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 c...
Need Help
  • PaulDBrown's avatar
    PaulDBrown
    4 years ago

    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...

PaulDBrown's avatar
PaulDBrown
Community Champion
4 years ago

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's avatar
Anonymous
Not applicable
4 years ago

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's avatar
    PaulDBrown
    Community Champion
    4 years ago

    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...