Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
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] )
Solved! Go to Solution.
Thank you. I was able to simplify it and got it to work. This is what I ended up with - works great.
Thank you. I was able to simplify it and got it to work. This is what I ended up with - works great.
Both Shifts[UID] and NS_Customer[ID] columns would need to be of type "Whole Number" for this to work. Alternatively all your tests should have double quotes in them.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
19 | |
15 | |
13 | |
11 | |
9 |
User | Count |
---|---|
25 | |
22 | |
12 | |
11 | |
10 |