Forum Discussion

Unknown_1's avatar
Unknown_1
Frequent Visitor
2 years ago
Solved

Need help on Lookupvalue dax

Hi Team,   I have a dataset of 3 columns a   s stated in the picture attached. I want to do a simple vlookup of the second column in the entire range and want to return the result(Third co...
Need Help
  • jgeddes's avatar
    jgeddes
    2 years ago

    Yes, blank rows will also cause that issue with LOOKUPVALUE.
    You can try something like...

    Calculated Column 2 = 
var _vTable =
SUMMARIZE(
    'Table',
    'Table'[Column1],
    "_value", SUM('Table'[Column3])
)
var _result =
SUMX(
    Filter(_vTable, [Column2] = [Column1]),
    [_value]
)
Return
_result

    Let me know how that works.

jgeddes's avatar
jgeddes
Super User
2 years ago

That error is indicating that there are values in Column2 that appear more than once in Column1. If this is the case then LOOKUPVALUE will not work. You will need to decide how you are going to aggregate the data in Column3 and then use the appropriate function (SUM, AVERAGE etc.).

Unknown_1's avatar
Unknown_1
Frequent Visitor
2 years ago

I have cross checked the dataset there is only 1 matching value present in column 2 which is appearing in column 1. However there are a blank row. Is that causing the problem?

Also is there any other way in which I can solve this problem?

  • jgeddes's avatar
    jgeddes
    Super User
    2 years ago

    Yes, blank rows will also cause that issue with LOOKUPVALUE.
    You can try something like...

    Calculated Column 2 = 
var _vTable =
SUMMARIZE(
    'Table',
    'Table'[Column1],
    "_value", SUM('Table'[Column3])
)
var _result =
SUMX(
    Filter(_vTable, [Column2] = [Column1]),
    [_value]
)
Return
_result

    Let me know how that works.

    • Unknown_1's avatar
      Unknown_1
      Frequent Visitor
      2 years ago

      Thanks it worked!