Forum Discussion
Help with a Switch formula
I am trying to add a column to a report. The main data for the column is a table called Customers. I have another table called Shifts. I need all occurrances of the User field in Shifts to populate on the report. The relationship between these tables is Customers.NetSuiteSiteId = Shifts.uid. There are several instances where Customers.id does not have correlating data. I want to hard code some values based information found in Shifts.uid. This is the formula I am using and below that the error message I'm receiving. I'm not sure what I am doing wrong. Both of the Customers.id & Shifts.uid are numeric fields.
| TempDept = |
| IF( |
| ISBLANK(Customers[id]), |
| VAR ShiftUID = |
| CALCULATE( |
| MAX(Shifts[uid]), |
| FILTER( |
| Shifts, |
| VALUE(Shifts[uid]) = VALUE(Customers[NetSuiteSiteID]) |
| ) |
| ) |
| RETURN |
| SWITCH( |
| TRUE(), |
| ShiftUID IN {26, 261, 384}, 9255, |
| ShiftUID = 88, 9258, |
| ShiftUID = 259, 22434, |
| ShiftUID = 350, 15994, |
| ShiftUID = 352, 214464, |
| ShiftUID = 356, 9259, |
| ShiftUID = 403, 214466, |
| Customers[id] |
| ), |
| Customers[id] |
| ) |
Error mussage is : Function 'CONTAINSROW' does not support comparing values of type Integer with values of type Text. Consider using the VALUE or FORMAT function to convert one of the values.
I started with this formula and received an error message of :
"A single value for column 'uid' in table 'Celayix_Shifts' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result."
TempDept =
IF(
ISBLANK(NS_Customer[id]),
SWITCH(
TRUE(),
Celayix_Shifts[uid] IN {26, 261, 384}, 9255,
Celayix_Shifts[uid] = 88, 9258,
Celayix_Shifts[uid] = 259, 22434,
Celayix_Shifts[uid] = 350, 15994,
Celayix_Shifts[uid] = 352, 214464,
Celayix_Shifts[uid] = 356, 9259,
Celayix_Shifts[uid] = 403, 214466,
NS_Customer[id]
),
NS_Customer[id]
)
Thank you. I was able to simplify it and got it to work. This is what I ended up with - works great.
