Looking Up Value from Another Table and Searching Column

[SopandaT]

Hello,

I'm trying get a column to search for a specific value in another table and if it finds that matching value, it will take the value in a specific column and apply it to the new column. I basically have a table with data that is mixed up together based on the 'Work Item Type' so I duplicated the table and filtered the other table to only show a specific 'Work Item Type'. The duplicated table is linked by a releated Work ID to the first table. I'm pulling data from VSTS if that helps.

[v-jiascu-msft]

Hi @SopandaT,

Could you please mark the proper answer as solution or share the solution if it's convenient for you? That will be a big help to the others.

Best Regards!
Dale

[jennympho]

the problem I had with LOOKUPVALUE function is that I could not reference to a column in a table under search value.  I wanted to look up the same value in another table in one column and return the result of another column in that another table.  Not sure how I could do it. 

[Greg_Deckler]

@jennympho- Can you explain that again with maybe an example?

[jennympho]

@Greg_Deckler

Hi Greg,

this is how I have resolved the problem at the end but i wonder if there's a quicker method.

I have 2 tables.

Table 1 - has 2 columns - account number and amount

Table 2 - has 2 columns - account number and whether overhead cost applies to that account number

I wanted to create a formula that will add the overhead cost to the amount if it should.

So, now this is how I have figured out to solve the issue:

1.  I added a calcuated column and use a LOOKUPVALUE function in table 1 to see if overhead cost should apply

2.  I added another calculated column to calcuate the overhead cost (amount x overhead rate) if overhead cost should apply

I wonder if this can be done in 1 formula and in 1 calculated column.

thanks for sharing your knowledge in advance!
Jenny

[Greg_Deckler]

LOOKUPVALUE is probably your best bet.

https://msdn.microsoft.com/en-us/library/gg492170.aspx

[SopandaT]

I'm curently using LOOKUPVALUE and I get the error message "LookUpValue does not support comparing values of type integer with values of type text".Does that mean one of my columns that I picked is not the same as the other formats? I've tired using the expression FORMAT on that numeric column but I don't know what to put for the second condition if that makes sense. 

FORMAT(Value,Format) I don't know what to put in format.

[Greg_Deckler]

You could use VALUE to convert from text to numeric, would have to see the data to see what is going wrong there.

[emadrigal]

why dont you create a copy of that column with the same datatype create the relation and then hide it?