most current version of name

Question

I have a database that has Item number and Item name among other fields, I'm creating a report that shows the qty of the item number by year. We have changed the item names a few times each year to reflect a more updated named but when I insert the Item name I get all the different name types, and I just want the most current name. Any help or suggstions would be great. Thanks

anonymous · Answer

I'm sure there's a simpler method, but the following as a new column should give you the current name for each Item:Current NAME =
    LOOKUPVALUE (
        'DataTable'[NAME],
        'DataTable'[Date], CALCULATE (
            MAX ( 'DataTable'[Date] ),
            FILTER (
                ALL ( 'DataTable' ),
                'DataTable'[ITEMID] = EARLIER ( 'DataTable'[ITEMID] )
            )
        )
    )

shawnprice · Answer

Steve I get a "A table of multiple values was supplied when a single value was expected." Any clue what the might be. Thanks

anonymous · Answer

Yeah, this is where it starts to get messy. You probably have two "latest" records for an ITEMID with the same last date, so the LOOKUPVALUE freaks out as it doesn't have a single row from wihich to choose the NAME. There's discussion on this "Last Non Empty" problem in several places - e.g. https://blog.crossjoin.co.uk/2013/01/15/a-different-approach-to-last-ever-non-empty-in-dax/

Do your records have a (unique) DateTime stamp rather than Date that can be used to select the latest matching ITEMID record?

Otherwise, do you have an Index column that can be used to select the last matching ITEMID record?

anonymous · Answer

Try this, for the last NAME listed for each ITEMID:Current NAME =
    CALCULATE (
        LASTNONBLANK ( 'DataTable'[NAME], 1 ),
        FILTER (
            ALL ( 'DataTable' ),
            'DataTable'[ITEMID] = EARLIER ( 'DataTable'[ITEMID] )
        )
    )

v-haibl-msft · Answer

ShawnPrice&nbsp;You can add an index column in your table then create a measure to get the latest name.&nbsp;&nbsp;Current NAME_Meaure = 
    VAR TempIndex =
        CALCULATE (
            MAX ( Table1[Index] ),
            ALLEXCEPT ( Table1, Table1[DELIVERYNAME (groups)], Table1[ITEMID] )
        )
    RETURN
        LOOKUPVALUE ( Table1[NAME], Table1[Index], TempIndex )&nbsp;Best Regards,Herbert&nbsp;

Forum Discussion

most current version of name

7 Replies

Related content

Version control

Fabric Warehouse - table versions

Is ADF Fabric version compactable with Github Proxima on version control?

Apache airflow new version and private endpoint support

version control