Forum Discussion
Help with a calculated column (lookup function?)
Good morning,
I have a table (Product_Structure) that contains repeat part number combinations (Parent Part + Child Part) because it esentially keeps a log of all the times the part number pairing was entered as well as the data for the part number pairing at that time. From this table, I only want to use the most recent data for the part pairing because that is the only data that is relevant. Each 'Parent Part" has several "Child Part" numbers, this is ok.
I want to make it so that each parent part and child part pairing is only used once in the table. This gets tricky because I want to make sure that the pairing used is the most recent pairing. I would need to be able to connect the most recent pairing's child part number to a part number from another table. I would also need to refresh this table in the future and have it update appropriately. I have attached the two tables for reference.
I have also attached an example of a repeat pairing that I would only want the most recent entry from (06/01/2023). Is someone able to tell me how I could create a column or some type of data filter/transformation to get the results I am looking for?
5 Replies
Stephanie-curno Maybe this: (2) Lookup Min/Max - Microsoft Fabric Community
Otherwise, Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882
Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.For reference the above is the table. I created a merged column which I think may help. There are multiple duplicates of the values in the merged column, but I only want the most recent instance to show up in the table (based on modification date). What is hard is I need to be able to refresh this table and have it automatically update.
you can try this
Column = if( CALCULATE(max('Table'[creation date]),ALLEXCEPT('Table','Table'[parent part],'Table'[child part]))='Table'[creation date],1,0)Hi,
Share some data to work with and show the expected result.
For reference the above is the table. I created a merged column which I think may help. There are multiple duplicates of the values in the merged column, but I only want the most recent instance to show up in the table (based on modification date). What is hard is I need to be able to refresh this table and have it automatically update.
