cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors