LOOKUPVALUE Error: A table of multiple values was supplied where a single value was expected.

Hi,

I am doing a very simple query and I get the above error. There are three tables with matching keys. What I am doing is seeing if the 'Functional Area' column is a certain value, then go and lookup the table that corresponds to that column, depending on the value.

If ([Functional Area]="Agreements", LOOKUPVALUE(agreements[PL Issues], agreements[WorkRequestID], [ID]), "false")

I have managed to do the LOOKUPVALUE fine on its own without the IF statement, which works without the error:  = LOOKUPVALUE(agreements[PL Issues], agreements[WorkRequestID], [ID]), However, that does not meet requirements as I need to check other tables too. I would rather code this properly than insert unique code in 6 different columns.

But upon using the IF, then this error appears: Calculation error in column 'WR'[]: A table of multiple values was supplied where a single value was expected.

|

* Please try a lower page number.

* Please enter only numbers.

* Please try a lower page number.

* Please enter only numbers.

Use this form to get the cell in that row:

=IF([@[Functional Area]]="Agreements", LOOKUPVALUE(agreements[PL Issues], agreements[WorkRequestID], [ID]), "false")

Kevin

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

Thank you for that! I was excited that such a simple solution would elegantly fix the problem; yet now I get a syntax error at the end:

[formatRiskID]) ,  "FALSE")

The syntax for ',' is incorrect.

The calculated column 'WR[IssueRisks]' contains a syntax error. Provide a valid formula.

//note: the incorrect comma is bolded above. I am confused at how a standard comma at the end of the if statement causes the error.

I further tried isolating the problem by removing the lookup - then I get "the end of the input has been reached with this:

=IF([@[Functional Area]]="eAgreements", "True", "FALSE")

Only when I remove the ampersand additions the code does  not throw an error. Any other thoughts?

//No error:

=IF([Functional Area]="eAgreements", "TRue", "FALSE")

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

I can't reproduce. See example:

www.zorvek.com/excel/gabrielhaukness.xlsx

Kevin

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

Ah hah! Looking at your example, I see I missed specifying an important detail (my apologies). I am writing this DAX calculated column in the data model and not in a normal pivot table worksheet.

The errors I was referencing above were occurring in the data model.

I did some additional research and found that the right path using DAX is something like this:

=IF([Functional Area]="eAgreements", COUNTROWS(FILTER(agreementsIR, agreementsIR[Work Request]=[formatRiskID])), BLANK()  )

That returns the number of many items mapping back to the single one item as an integer. In the above example if a Work Request has 4 issues; I get back a 4.

however, I am still not seeing in DAX how I get back multiple values of cells from the 4 items that get returned. VALUE and DISTINCT both do not return back the value that the LOOKUPVALUE does - what I essentially need to do is a FOR EACH LOOKUPVALUES on every cell in my table.

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

Hi,

Upload the file to OneDrive and share the link of the uploaded file here.

Regards,

Ashish Mathur
www.ashishmathur.com
http://twitter.com/excelashish

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

I'm not well versed in DAX so I've called in one of my buddies, a DAX guy, to take a look. He should post soon.

Kevin

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

Hi there,

Let's start with the reason you're getting an error. The problem is you're trying to write a DAX measure, which is used for aggregation, and you want a table of values, which would need a calculated column. So as a DAX measure you'd need to aggregate it somehow, whether that's inside a SUM(), or AVERAGE(), or whatever you want to perform on it.

The FILTER() function will return a table. Since you're nesting that inside a COUNTROWS() function, you'll only get a count, which is an aggregation, hence no errors.

You could use a calculated column for this, and set your condition there. Assuming your tables are related properly, you could use the RELATED() function to bring in those values from the other table, as a means for getting the data consolidated to a single table.

So lets assume you have three tables named Table1, Table2 and Table3 (I only see two names you've given, 'agreements' and 'agreementsIR', so I'm not sure what the third would be). Assuming Table1 is the primary table of data, and you have all three tables related properly, in Table2 you could use:

Col1Name:=RELATED(Table3[ColumnName])

Then in Table1 you could add two calculated columns:

Col2Name:=RELATED(Table2[ColumnName])

Col3Name=RELATED(Table2[Col1Name])

* Note how the name of the column in the third calculation is pointing to the first calculated column name, so you'll need to update these accordingly.

Then as a calculated column you could do:

=IF([Col2Name]=[Col3Name],RELATED(Table2[ColumnToReturn]),BLANK())

This assumes your relationships are going in the right direction as well, otherwise the calculated columns will need to go in the opposite tables of the direction of the relationship. Then you can just use the calculated column which should only return values of matching all three table (columns).

Can you explain exactly what it is you're trying to do here? This would probably shed some light as to the best solution for you.

Does this help?

Zack Barresse

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

Here's a link to a sample file showing the results to a PivotTable:

http://1drv.ms/1gI0Ibx

Zack Barresse

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

Hey guys, thanks so much for your replies and help. Backing up and going into the data model and previous problems would probably help. For reference I am including the

diagram view

relationships

What needs to happen is that I need to 1) use a calculated column existing in the parent table 'WR'. The calculated table is derived from values on the various childIR tables. 2) Combine the values from the 6 childIR tables into a single view for use in a PowerView. PowerPivot does not cut it for my users because once you start having to select from 6 tables, it is not intuitive for most business folks.

Firstly, when I created the relationships from WR to the childIR - it always states 'The relationship cannot be created in the requested direction. When you click create the direction will be reversed'

At this point I went ahead and created the calculated DAX column in the child IR tables. for documentation sake here that is:

= IF(RELATED(WR[PLItemID])>0,IF([Issue Status]="Active", IF(OR([Status]="In progress",[Status]="Not Started"),CONCATENATE(RELATED(PL[Title]),CONCATENATE(": ",[Title])), "Exclude - PL issue risk Status Complete"),"Exclude - Issue Status not active"),IF([Issue Status]="Active", IF(OR([Status]="In progress",[Status]="Not Started"), CONCATENATE("Business Relevance Issue/Risk: ",[Title]), "Exclude - WR issue risk Status Complete"),"Exclude - Issue Status not active"))

The roadblock I have is that I need to get the calculated column in each of the 6 childIR tables and pull that back into the main table as a single column. This would enable the PowerView to work as we need it to.

I tried to do a RELATED() but then this happens: The column 'agreementsIR[Title]' either doesn't exist or doesn't have a relationship to any table available in the current context.

It sounds like from the comment about the relationships going the right way I need to reverse their direction. If I could do that then most of the IF function above in the code block could work with dropping and adding some RELATED. That would allow for a single calculated column in the parent table and an IF statement to find the mapping to the child, then run the IF function (IF column=child, then run the code block with relationships to child)

Right now I have ended up with 6 buttons and a macro that clears out the pivot table and inserts the selected child values. But I know there must be some way so that the parent table can pull in the child values all in a single table, right?

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

Hi,

Can you upload your file to OneDrive and share the link of the file here?

Regards,

Ashish Mathur
www.ashishmathur.com
http://twitter.com/excelashish

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

* Please try a lower page number.

* Please enter only numbers.

* Please try a lower page number.

* Please enter only numbers.

 
 

Question Info


Last updated June 27, 2021 Views 23,394 Applies to: