Forum Discussion

NotVeryGoodatBI's avatar
NotVeryGoodatBI
Frequent Visitor
2 years ago
Solved

Need Help With LOOKUPVALUE or need ideas

Hello, I am a little new to PowerBI and I am trying find a function like Excel's VLOOKUP that will allow me to create a column in Invoice Table 1 that pulls correct margin value from the time frame o...
LOOKUPVALUE
powerbi
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi NotVeryGoodatBI ,

    Please try the following methods and check if they can solve your problem:

    1.Create a new column. Enter the following DAX formula.

    Margin_table(Margin) = 
CALCULATE(SUM('Margin_Table'[Margin]),
FILTER('Margin_Table',
'Margin_Table'[ID]='Invoice_Table'[ID]&&'Margin_Table'[start_date]<='Invoice_Table'[start_date]&&'Margin_Table'[end_date]>='Invoice_Table'[start_date]))

     

    2.Drag the new column to the Invoice_Table.

    3.The result is shown below.

     

    Best Regards,

    Wisdom Wu

     

     

     

     

     

     

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi NotVeryGoodatBI ,

If I understand correctly, the issue is that you want to find a function to create a column. Please try the following methods and check if they can solve your problem:

1.Create a new column. Enter the following DAX formula.

 

Column = LOOKUPVALUE(Margin_Table[Margin], Margin_Table[ID], Invoice_Table[ID], Margin_Table[start_date], Invoice_Table[start_date])

 

 

2.Drag the column into the table visual.

3.The result is shown below.

4.You can also use the following link to learn the function.

LOOKUPVALUE function (DAX) - DAX | Microsoft Learn

 

If the above ones can’t help you get it working, could you please provide the desired result screenshot to make a deep troubleshooting? It would be helpful to find out the solution.

 

Best Regards,

Wisdom Wu

 

 

 

 

 

 

NotVeryGoodatBI's avatar
NotVeryGoodatBI
Frequent Visitor
2 years ago

Hello v-jiewu-msft, 

Thank you for your comment. The desired output is below. I need a calculated column function that will match the ID from Invoice_Table1 to the ID in Margin_Table2, as well as filter MarginTable2 for the time frame listed in Invoice_Table1, grabbing the margin each row by matching the ID and timeframe.

invoice_numberIDstart_dateend_dateMargin_table(Margin)
5456110/20/202311/20/20230.32
511521/19/20242/19/20240.62
1515633/25/20244/26/20240.16
515541/18/20242/22/20240.54
46565511/20/202312/10/20240.57
PREVIEW
 
 
 
  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi NotVeryGoodatBI ,

    Please try the following methods and check if they can solve your problem:

    1.Create a new column. Enter the following DAX formula.

    Margin_table(Margin) = 
CALCULATE(SUM('Margin_Table'[Margin]),
FILTER('Margin_Table',
'Margin_Table'[ID]='Invoice_Table'[ID]&&'Margin_Table'[start_date]<='Invoice_Table'[start_date]&&'Margin_Table'[end_date]>='Invoice_Table'[start_date]))

     

    2.Drag the new column to the Invoice_Table.

    3.The result is shown below.

     

    Best Regards,

    Wisdom Wu