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

[Anonymous]

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 2019	123
05 May 2018	123
03 February 2019	567

Sheet 2:

ID      Start Date             End Date                   Name

123	01 January 2019	31 December 9999	Red
123	01 January 2018	31 December 2018	Blue
567	01 January 2018	31 December 9999	Orange

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 2019	123	Red
05 May 2018	123	Blue
03 February 2019	567	Orange

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!

[RodrigoMachado]

That worked for me too! I had a very similar issue, and I alse was trying to use a DAX very similar to what @Anonymous was trying. It is funny how we cannot use something like LOOKUPVALUE to achieve those needs of retrieving certain values... In excel, a PROVC would do the work

[Zubair_Muhammad · Accepted solution]

@Anonymous 

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],
    ","
)

[Anonymous]

Terrific it works!