A single value for column 'store' in table 'upsAddresses' cannot be determined.

[croberts21]

I'm fairly new to PBI. Some concepts are new to me. I'm used to using a bit of SQL with Perl.

PBI app v2.100.1182 (64 bit, December 2021) on Windows 10.

I have data sources both from 2 Excel spreadsheets. One table from one Excel file is called InputFile. One table from the other file is UPSAddresses. I have to look up an address in UPSAddresses by linking the [7-digit aim] in InputFile to the corresponding column in UPSAddresses.

When adding UPSAddresses[store] to ShiptoName using & operator, I get the error "A single value for column 'store' in table 'upsAddresses' cannot be determined. This can happen when a measur eformula refers to a column...". I have 2 tables, one named "InputFile" and one named "UPSAddresses". I defined a relationship between InputFile[7-digit aim] and UPSAddresses[aim]. The relationship is many to one from InputFile to UPSAddresses,, cross filter direction is "Both". For each [7-digit aim] in InputFile there should be one [aim] in UPSAddresses.

I'm using a custom column like this in my table visualization: ShipToName = "AIM# " & InputFile[7-DIGIT AIM] & " "

This part works.

When I change it to this: ShipToName = "AIM# " & InputFile[7-DIGIT AIM] & " " & UPSAddresses[store]

is when I get the error above. Both InputFile[7-digit aim] and UPSAddresses[store] are all numeric. At least they are supposed to be, I didn't check all 2200+ records. Both files are data from the customer so anything could be in there.

- Could this be caused by more than one UPSAddresses[store] that matches the InputFile[7-digit aim]?

- How do I fix this? I need to make use of the "&" operator to put together strings fairly often.

Thank you!

[croberts21]

It worked! Thank you.

[croberts21]

Thank you, I will give it a try.

[MFelix · Accepted solution]

Hi @croberts21 ,

In calculated columns you cannot use values from another table directly even if they are related in this case you are getting multiple values because you are refering the full table so more than one value per line. Instead of refering to the column directly use the RELATED syntax redo your column to:

ShipToName = "AIM# " & InputFile[7-DIGIT AIM] & " " & RELATED(UPSAddresses[store])