Getting error in switch Fn

[Anonymous]

Hi,

I am getting error "The Expression Refers to Multiple Columns. Multiple Columns Cannot Be Converted to a Scalar Value."

in below function

Select Metric = IF(ISCROSSFILTERED('Metric '),

SWITCH(TRUE(),

VALUES('Metric ') = "Sales($)",ROUND([Select Sales],0),

VALUES('Metric ') = "Vol",ROUND([Select Trans],0),

VALUES('Metric ') = "customers",ROUND([Cust],0),

VALUES('Metric ') = "Sales Conversion(%)",ROUND([Sales Conversion],0),

VALUES('Metric ') = "AVG VOL($)",ROUND([Avg Vol],0),

VALUES('Metric ') = "Exit",ROUND([Exit],0), BLANK()),BLANK())

[MartynRamsden · Accepted solution]

Hi @Anonymous 

I think you'd be better off using the SELECTEDVALUE function, as per below.

Select Metric =
VAR SelMetric = SELECTEDVALUE ( 'Metric ' )
VAR Result =
    SWITCH (
        TRUE (),
        SelMetric = "Sales($)", ROUND ( [Select Sales], 0 ),
        SelMetric = "Vol", ROUND ( [Select Trans], 0 ),
        SelMetric = "customers", ROUND ( [Cust], 0 ),
        SelMetric = "Sales Conversion(%)", ROUND ( [Sales Conversion], 0 ),
        SelMetric = "AVG VOL($)", ROUND ( [Avg Vol], 0 ),
        SelMetric = "Exit", ROUND ( [Exit], 0 ),
        BLANK ()
    )
RETURN
    Result

Best regards,
Martyn

[Anonymous]

Excellent Mate, Thanks Much