Forum Discussion

SPC_123's avatar
SPC_123
Frequent Visitor
6 years ago
Solved

LOOKUPVALUE

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!

 

 

 

 

Need Help
  • parry2k's avatar
    parry2k
    6 years ago

    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!

3 Replies

  • parry2k's avatar
    parry2k
    Super User
    6 years ago

    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!

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    6 years ago

    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