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?
