Forum Discussion
Getting error in switch Fn
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())
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 ResultBest regards,
Martyn
2 Replies
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 ResultBest regards,
Martyn