Problem with RELATED function

[MsDSmith]

After struggling with this for two days I'm turning it over to the experts.

I have two relatively simple tables in PowerPivot. Table 1 has labor costs by project and table 2 has the fringe rates for each project. I would like to add a calculated column to table 1 that shows the fringe cost, which is calculated by multiplying the labor cost (table 1) by the fringe rate for that project (table 2). I thought it would be simple but I keep getting a blank column. I am certain that the text in the relationship column matches exactly (it is an alpha-numberic id code). The columns with the relationship are defined and formatted as text. The columns being used in the calculation as well as the calculation column itself are defined as decimal number. I have tried everything including starting a new PowerPivot from scratch. Every time I am getting the same result - a blank column. The formula I'm using is:

RELATED('Project Fringe-Indirect Rates'[Fringe Rate])*'Job Cost Data'[MeasuresTotal Cost]

I couldn't figure out how to upload an image of the tables so I'm hoping you can go off of what I've posted. I believe the problem is with the relationship because even when I simply use the RELATED function to pull in data from any column in table 2, I'm getting blanks. I just can't figure out what the problem is. What are the problems/mistakes that could cause this to happen?

Any and all suggestions are very much appreciated.

 

[powerpivotpro]

Typically this means the values in the two columns don't actually match. (I mean the columns that form the link of the relationship).

Check the data types of those two columns. Sometimes if one is Text and the other is Number, that is the problem. Other times I have seen Dates in one table and Date/Time in the other. Or Decimal versus Whole Number.

And to be clear, I do NOT mean the Formatting option. I mean the Data Type option above that on the Power Pivot ribbon.

 

[MsDSmith]

Thanks to your response I continued my quest to figure out how the cells could be different,especially given that I created the second table by copying and pasting the related column from the first table. I found a great excel add-in called CellView (CellView Add In) that allowed me to see the decimal code within each cell. I discovered that a space in the cells of the second table had a different decimal code (160 vs. 032 in case anyone cares). I simply copied a space in the cell that had the right code and did a find and replace for the one that had the incorrect code. Problem solved and my formulas are working just fine now.

Thank you!

 

[powerpivotpro]

Glad to hear it. Also, that was pretty **** hardcore, the way you tracked that down

Oh come on! I can't say "d*mn" on this board??? I'm gonna go talk to MrExcel right ****ing now!

 

[MsDSmith]

Hello,

I'm back again. I posted this original question about a calculated field coming up blank when using the RELATED function. The problem has returned and I cannot figure it out this time. I updated the data and got blanks again. No problem I thought. I know how to fix this. But my last solution didn't work.

To review, I have table 1 pulling from a SQL database with many lines of data, all of which contain a task description. On table 2 there is a list of all the different tasks (no repeats) along with a fringe rate for each one. This data was copied and pasted in so it is not linked to or updating from anything. I'm trying to use the RELATED function to calculate a fringe rate for each line on table 1. I've even tried just getting it to pull the fringe rate for each line and it keeps coming up blank. Clearly the RELATED function isn't finding a match between the two tables for some reason. I have confirmed that the task descriptions in each table are identical. How you ask? Well, multiple ways. I copied the tasks from both tables into another spreadsheet and did a vlookup between the two and it worked. I did an IF(A=B, 1,0) and got matches across the board. I used the nifty CellView add in and confirmed that the decimal code in each is exactly the same. The task descriptions are alpha-numeric such as "05-04-01-01 Conference Planning" if that makes any difference. I have confirmed that the data type and format for both columns is text. PowerPivotPro said in his first response that "typically" this means the columns don't actually match. Is there anything else that can be causing this?

This is our most desperate hour. Help me Obi-Wan Kenobi. You're my only hope.

 

[powerpivotpro]

Can you send me the workbook?

 

[skeetcannon]

Can you send me the workbook?

Resurrecting this thread from the depths of the internet :

I am having a similar with the RELATED function returning blanks, when relating to a SQL Table.

Were you two ever able to find a resolution for RELATED function?

Thank you, guys!

 

[scottsen]

Would have probably started a new thread, but as Rob said long ago, this typically just means that the fields you related on don't match as well as you think. Can you share a workbook?

 

[MsDSmith]

Resurrecting this thread from the depths of the internet :

I am having a similar with the RELATED function returning blanks, when relating to a SQL Table.

Were you two ever able to find a resolution for RELATED function?

Thank you, guys!

I sent Rob the workbook and after spending some time with it he admitted that the problem stumped him. He was going to consult with someone he knew at MSFT about it but I never heard back from him. Unfortunately, this problem turned me off to the powerpivot so I've since abandoned efforts.

 

[scottsen]

That is like... sad n stuff

Let's make sure that doesn't happen again. SkeetCannon hook me up.