Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
No time stamp only the date. I will try all those options, thanks very much for the help and I'll get back to you guys to let know if it solved my issue or not.
You can add an index column in your table then create a measure to get the latest name.
Current NAME_Meaure = VAR TempIndex = CALCULATE ( MAX ( Table1[Index] ), ALLEXCEPT ( Table1, Table1[DELIVERYNAME (groups)], Table1[ITEMID] ) ) RETURN LOOKUPVALUE ( Table1[NAME], Table1[Index], TempIndex )
Best Regards,
Herbert
Herbert this looks like its working from the limited tests I've run. I'll let you know. Thanks.
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] ) ) ) )
Steve I get a "A table of multiple values was supplied when a single value was expected." Any clue what the might be. Thanks
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?
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] ) ) )
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |