A table of multiple values was supplied where a single value was expected (With a twist...)

[Brett007]

I have a formula that I am copying from the original dataset to the Archive Dataset (stores the original dataset once a week) Same table names just "Archive" added to the front of their names.  

My question is how would I resolve this error when it works in the Main Dataset?  

This is the calculated Column:

Milestone Latest Disposition =

VAR flag = CALCULATE(MAX('archiveddatasettable1'[Disposition #]),

FILTER('archiveddatasettable1',

EARLIER('archiveddatasettable1'[Milestone History Lookup]) = 'archiveddatasettable1'[Milestone History Lookup]))

RETURN

LOOKUPVALUE('archiveddatasettable1'[Milestone Disposition Status],

'archiveddatasettable1'[Disposition #],

flag,

'archiveddatasettable1'[Milestone History Lookup],

'archiveddatasettable1'[Milestone History Lookup])

All of the Values are pulled from the same table.  Other calculations work just fine.  

If I go into the dataset 'Transform Data' and filter it to just one month then the calculation works.  I think that should tell me something, but I don't know where to turn next.

[v-alq-msft · Accepted solution]

Hi, @Brett007 

You may try the following calcualted column to see if it works.

Milestone Latest Disposition =
VAR flag =
    CALCULATE (
        MAX ( 'archiveddatasettable1'[Disposition #] ),
        FILTER (
            'archiveddatasettable1',
            EARLIER ( 'archiveddatasettable1'[Milestone History Lookup] ) = 'archiveddatasettable1'[Milestone History Lookup]
        )
    )
RETURN
    CALCULATE (
        MAX ( 'archiveddatasettable1'[Milestone Disposition Status] ),
        FILTER (
            ALL ( 'archiveddatasettable1' ),
            'archiveddatasettable1'[Disposition #] = flag
                && 'archiveddatasettable1'[Milestone History Lookup]
                    = EARLIER ( 'archiveddatasettable1'[Milestone History Lookup] )
        )
    )

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[HotChilli]

I think we have to asume it's the LOOKUPVALUE

-> LOOKUPVALUE looks for a single value satisfying the conditions in the target table. In case the search returns multiple values, you obtain an obscure error message saying: “A table of multiple values was provided where a single value was expected”.

The data will be different in the archive data compared to the live set and there are multiple values returned.

[Brett007]

What would you suggest I use to replace the LOOKUPVALUE?  Can I add another calculation within or outside of the LOOKUPVALUE to correct this issue?