Why do I get "Single value can't be determined"?

[andrewbt]

Hi all

Hoping you can help with my understanding of a problem I'm having related to adding data to a table using a one to many relationship.  

Maybe easiest to show an example where I have two tables

• "Unique" containing unique ID values and a second data item "name" for each
• "Many" where there are multiple entries for the IDs and values for those  

The relationship correctly recognises the 1 to many nature, and I was trying to pull the name into the Many table by using a calculated column ZLinkName='Unique'[Name].  It didn't work and I got the error "a single value can't be determined" as you can see in the screenshot.   I don't understand it because I thought the nature of the relationship was that there are only single values in the "Unique" table.

I solved in in other ways: using a lookup (ZLookupName in the table below) and also using a visualisation where I could pull in the values from "Many" then Name from "Unique" without any problems.  Can anyone explain what I'm missing in terms of how the relationship works and why the calculated column fails?

Thanks a lot

Andrew

Table relationships and values

[amitchandak]

@andrewbt , you can nor do it like this. This can only be done within the table

try

new column = related (Unique[Name])

[PaulDBrown · Accepted solution]

@andrewbt 

The reason you are getting an error is because the expression you are using in the new column, 'Unique' [Name], by definition returns all the values contained in the column of the table you are referring to in the expression; you need to define a function to return a single value from that column for each row (and not a list of values)

Tables are independent entities. The fact that there is a relationship means that when you use the dimension table in filters, slicer, measures etc, the selection is activated and filters are propagated "along the relationship" based on how you have set up the direction in the relationship itself in the model. The relationship also allows you to refer to the columns linked between tables as you are trying to do. Yet It's important to remember that when it comes to calculations, whether it be in a cell in a table or in measures, the result must be a single value (scalar value). If the expression lists a number of values, you will get this error.

(The exception to this is when you are creating a new table, where a list of values is expected)

Since the tables are related by a relationship, to find the corresponding value for each row you can use:

ZLinkname = RELATED('Unique' [Name])

The function RELATED uses the relationship to find the corresponding value in the related table (and it can be any column in the related table, for example 'Unique' [ID] in your example), not just the value in the column which has the relationship).

There are other ways of looking up a value like LOOKUPVALUE, TREATAS etc...which can be used regardless of whether there is a relationship between two tables.

I hope that helped somewhat!

[andrewbt]

Thanks @PaulDBrown  for the very clear explanation - I understand!  In the past I worked a lot with different relational databases and need to keep reminding myself that this is sometimes quite different 🙂

[andrewbt]

Thanks @amitchandak !

[Pragati11]

Hi @andrewbt ,

The reason for your error is there are multiple values in 2nd table for a single value of your column in 1st table.

Try using FIRSTNONBLANK rather than LOOKUPVALUE.

There is an existing thread on how to use FIRSTNONBLANK

https://community.powerbi.com/t5/Desktop/Lookupvalue-only-first-result/td-p/244009

Thanks,

Pragati

[andrewbt]

Thanks @Pragati11 and for the link to the other thread.