cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
MikeQB Helper I
Helper I

LOOKUPVALUE - "A table of multiple values was supplied where a single value was expected"

Hi All, 

 

I've read all of the previous threads in regards to this issue however I did not find any good solution that works for my setup. 

 

I have two tables:

 

Sheet 1:

Change Date           ID

05 May 2019123
05 May 2018123
03 February 2019567

 

Sheet 2:

ID      Start Date             End Date                   Name

12301 January 201931 December 9999Red
12301 January 201831 December 2018Blue
56701 January 201831 December 9999Orange

 

I want to create a calculated column that returns the Name in Table 1 for each ID that falls into the date range. 

 

Sheet 1 including calculated column should look like that:

Change Date           ID    Name

05 May 2019123Red
05 May 2018123Blue
03 February 2019567Orange

 

I have tried the following two approaches:

1:

Calc Name 1 = CALCULATE(Values(Sheet2[Name]),
FILTER(
ALL(Sheet2),
AND(Sheet1[Change Date] >= Sheet2[Start Date], Sheet1[Change Date] <= Sheet2[End Date])))
 
2:
Calc Name 2 = IF(
AND(Sheet1[Change Date]>= MIN (Sheet2[Start Date]),Sheet1[Change Date]<= Max (Sheet2[End Date])),
LOOKUPVALUE(Sheet2[Name],Sheet2[ID],Sheet1[ID]),BLANK())

 

Calc Name 1 works if I put a filter on Query for Sheet 2 to display only one of the IDs e.g. 123.

The moment I take off the filter in the query I get multiple values returned instead of the one that was expected.

 

What step am I missing here?

 

Thanks!

 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User III
Super User III

Re: LOOKUPVALUE - "A table of multiple values was supplied where a single value was expected&qu

@MikeQB 

 

Try this revision

 

Calc Name 1 =
CONCATENATEX (
    CALCULATETABLE (
        VALUES ( Sheet2[Name] ),
        FILTER (
            ALL ( Sheet2 ),
            Sheet1[Change Date] >= Sheet2[Start Date]
                && Sheet1[Change Date] <= Sheet2[End Date]
                && Sheet1[id] = Sheet2[id]
        )
    ),
    [Name],
    ","
)
Try my new Power BI game Cross the River

View solution in original post

2 REPLIES 2
Super User III
Super User III

Re: LOOKUPVALUE - "A table of multiple values was supplied where a single value was expected&qu

@MikeQB 

 

Try this revision

 

Calc Name 1 =
CONCATENATEX (
    CALCULATETABLE (
        VALUES ( Sheet2[Name] ),
        FILTER (
            ALL ( Sheet2 ),
            Sheet1[Change Date] >= Sheet2[Start Date]
                && Sheet1[Change Date] <= Sheet2[End Date]
                && Sheet1[id] = Sheet2[id]
        )
    ),
    [Name],
    ","
)
Try my new Power BI game Cross the River

View solution in original post

MikeQB Helper I
Helper I

Re: LOOKUPVALUE - "A table of multiple values was supplied where a single value was expected&am

Terrific it works! 

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Top Solution Authors
Top Kudoed Authors