Forum Discussion

Omelei's avatar
Omelei
Frequent Visitor
3 years ago
Solved

LOOKUP value in table from expression

Hi guys, 

 

I am new to PowerBI. Hope you can help. 

 

I created the following table:

 

Productconfig idProduct idQuantityOpportunity idPrevious Opportunity id (related from other table)Quantity of product in previous opportunity (this is the column I'd like to create)
abcdaa100xx  
defgbb100xx  
hijkcc200xx  
lmnoaa100yyxx100
pqrsaa150zzyy100
tuvwbb400zzyy 

 

Now I need to add another column with the quantity of the product from the previous opportunity.

 

I already took the following steps:

 

  • Created column with concatenate of Opportunity ID & Product ID
  • Created column with concatenate of Previous Opportunity ID & Product ID
  • Used LOOKUPVALUE to lookup quantity of product in previous opportunity

 

However, LOOKUPVALUE function does not work with expressions. 

 

Any suggestions?

Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    3 years ago

    Omelei I would try this:

    Column in Table = 
  VAR __PreviousOpp = RELATED('Other Table'[Opportunity id])
  VAR __Product = 'Table'[Product id]
  VAR __Result = MAXX(FILTER('Table',[Product id] = __Product && [Opportunity id] = __PreviousOpp),[Quantity])
RETURN
  __Result

1 Reply

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    3 years ago

    Omelei I would try this:

    Column in Table = 
  VAR __PreviousOpp = RELATED('Other Table'[Opportunity id])
  VAR __Product = 'Table'[Product id]
  VAR __Result = MAXX(FILTER('Table',[Product id] = __Product && [Opportunity id] = __PreviousOpp),[Quantity])
RETURN
  __Result