cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
croberts21
Super User
Super User

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

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!

1 ACCEPTED SOLUTION
MFelix
Super User
Super User

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])

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

3 REPLIES 3
croberts21
Super User
Super User

It worked! Thank you.

croberts21
Super User
Super User

Thank you, I will give it a try.

MFelix
Super User
Super User

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])

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Dev Camp Session 26

Check it Out!

Mark your calendars and join us on Thursday, September 29 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors