Forum Discussion

JOAP's avatar
JOAP
Icon for Helper I rankHelper I
6 years ago

Add a column using LOOKUPVALUE but in the same table where I also want to lookup matching values

Hi,

This one has been a real struggle for me.

I'm trying to add a new column in my finance facts table using DAX function LOOKUPVALUE, but the tricky thing is that I only want to look for values in that same table (finance facts). When I hardcode the #search value# as in the attached picture to "ST0151_1909AC_3001_2001_SEK" it works but that is of course not doable since it should be dynamic. Everytime I try to set #search value# to column IC_Match_Rec I get "A table of multiple values was supplied where a single value was expected". The values in column IC_Match_Rec are unique, so I cannot understand this?

 

Below works but #Search value# is hardcoded and should be referencing the column IC_Match_Rec but then "multiple values error"?

 

Grateful for any tips I can get :-)

Best Regards,

Johan

 

Need Help

4 Replies

  • AlB's avatar
    AlB
    Icon for Community Champion rankCommunity Champion
    6 years ago

    Hi JOAP 

    The column you need to check for unique values is IC_Match_Pay, since it's the one the filtering is being done on. Are you taking blank values into account? 

     

    Please mark the question solved when done and consider giving kudos if posts are helpful.

    Cheers  Datanaut

     

    • JOAP's avatar
      JOAP
      Icon for Helper I rankHelper I
      6 years ago

      Sorry that doesn't help. In Excel a SUMIFS() or VLOOKUP() would easily fix this but I struggle in PBI Desktop.

      Could maybe the SUMX() in combination with CALCULATETABLE() in PBI work?

       

      Below didn't work:

      • v-juanli-msft's avatar
        v-juanli-msft
        Icon for Community Support rankCommunity Support
        6 years ago

        Hi JOAP 

        You could create calculated columns

        Column = CALCULATE(SUM('Table'[amount]),FILTER('Table','Table'[IC_Match_Pay]<>BLANK()))

        or 

        Column 2 = CALCULATE(SUM('Table'[amount]),FILTER('Table','Table'[IC_Match_Pay]=EARLIER('Table'[IC_Match_Pay])&&'Table'[IC_Match_Pay]<>BLANK()))

         

        Or create a measure

        Measure = CALCULATE(SUM('Table'[amount]),FILTER('Table','Table'[IC_Match_Pay]<>BLANK()))
        Best Regards
        Maggie
        Community Support Team _ Maggie Li
        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.