Forum Discussion

JordanMacchio's avatar
JordanMacchio
Frequent Visitor
3 years ago

VLOOKUP Equivalent Formula Working except for one row

Hi all,

 

I am using the following formula: 

CALCULATE(FIRSTNONBLANK('Factory Output'[Production Line],1),FILTER('Factory Output','Factory Output'[Lookup]='SKU - Monthly S82 Actual Cost of Production'[Lookup] ))
 
I have 2 tabs. One is called factory output, which has which sku is produced in what month on what production line. Another is called the S82 Actual Cost of Production which has each SKU by month and the cost (no production line). I use this formula above to link the line that the product is produced on more in the given month (Sorted the rows by Volume in Power Query) as a calculated column in the S82 Actual Cost of Production table (I can't link the tables as it would be a many to many relationship and there are SKU's that can be produced on more than 1 line, sometimes more than 1 line in the same month as well).
 
The formula works as a VLOOKUP alternative but this month for some reason when updating the report, everything is working but there is 1 SKU not picking up a Production Line from the Factory Output table when the information is in the other table. Every other SKU in the Actual Cost of Production Report is picking up the appropriate production line, but this one SKU isn't picking it up properly for some reason. I have checked text/number formats, if the lookup values are the exact same, and everything lines up. I have tried exporting the data to excel to do a manual vlookup and it works for the SKU I'm having troubles with in PBI.
 
One note, I can't share the data as it's confidential but the data source is an SAP BW Report linked directly into Power BI. I tried exporting the data to excel and replicating the formula in a new BI workbook with the same tables but in an excel file and it worked for the SKU I was having issues with in the original PBI.
 
Is this a bug? Or is there something I'm completely missing?
Need Help

2 Replies

  • JordanMacchio's avatar
    JordanMacchio
    Frequent Visitor
    3 years ago

    Just an FYI, the value that isn't picking up the formula correctly happens to be the last line in the dataset as well

  • v-zhangti's avatar
    v-zhangti
    Community Support
    3 years ago

    Hi, JordanMacchio 

     

    Without you having to share the actual data, can you virtualize portions of the data to illustrate your problem? And what your expected output will be, you can show it with pictures or Excel.

     

    Best Regards,

    Community Support Team _Charlotte

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.