LOOKUPVALUE

[SPC_123]

I'm having an issue creating a new measure which replicates a previously created one.

The first/original measure (which is successful) is as follows -

USDV = LOOKUPVALUE(XEUSD[Units per USD],XEUSD[CC],'STL'[Currency])

When creating the 'new' measure in the same table...

EURV = LOOKUPVALUE(XEEUR[Units per EUR],XEEUR[CC],'STL'[Currency])

The following error is returned for the newly created EUR measure:

A single value for column 'Currency' in table 'STL' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.

In the above, both XEEUR[CC] and STL[Currency] contain Currency Code values.

In the modeling tab the relationship between XEEUR CC and STL Currency exists as it does with XEUSD CC.  Column formattings of XEUSD and XEEUR are identical.

Why would one work, but the next not follow suit?

Many thanks in advance!

[Greg_Deckler]

For some reason, your filtering criteria on your LOOKUPVALUE is not filtering down to a single value

EURV = LOOKUPVALUE(XEEUR[Units per EUR],XEEUR[CC],'STL'[Currency])

So, you can get around this using something like:

MAXX(FILTER('XEEUR',[CC] = 'STL'[Currency]),'XEEUR'[Units per EUR])

To see what duplicates there are, replace MAXX with CONCATENATEX

[parry2k · Accepted solution]

@SPC_123 you should be adding it as a column instead of measure

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

[SPC_123]

Thank you!