Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
andrewbt
Helper II
Helper II

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

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 valuesTable relationships and values

1 ACCEPTED SOLUTION
PaulDBrown
Community Champion
Community Champion

@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!

 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






View solution in original post

6 REPLIES 6
amitchandak
Super User
Super User

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

try

new column = related (Unique[Name])

PaulDBrown
Community Champion
Community Champion

@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!

 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






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 🙂

Thanks @amitchandak !

Pragati11
Super User
Super User

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

 

 

Best Regards,

Pragati Jain


MVP logo


LinkedIn | Twitter | Blog YouTube 

Did I answer your question? Mark my post as a solution! This will help others on the forum!

Appreciate your Kudos!!

Proud to be a Super User!!

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

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.