LOOKUPVALUE from same table?

macfuller

Active Member
Joined
Apr 30, 2014
Messages
319
Office Version
  1. 365
Platform
  1. Windows
I want to get a parent value into a new column based on a hierarchy in the same table, but the DAX documentation says LOOKUPVALUE doesn't work from the same table. I have a crude workaround but I'm thinking there must be something simpler.

We have a single table of Global Location Numbers (GLNs), the GS1 location standard. (Simplified version below) A ship-to location corresponds to a street address, a deliver-to to a location within that street address. Every ship-to can have multiple deliver-tos. Our orders are delivered to the deliver-to locations (duh) but we'd like to group deliveries by the street addresses.

The file has a Parent GLN column that has the GLN for the related ship-to address. We could group by just using parent GLN in the pivot table but it's a 13-digit number and pretty meaningless. So we'd like to do it by the location name of the parent GLN.

Sounds like a simple job for a new column ParentName using LOOKUPVALUE but that gives errors, probably for the reason given above. I have the formula below that works but it seems awfully complicated, so I'm wondering if there's a simpler option I've missed.

=
IF (
GLN[Location Type] = "Ship To",
GLN[Descr],
CALCULATE (
FIRSTNONBLANK ( GLN[Descr], 1 ),
FILTER ( GLN, GLN[GLN#] = EARLIER ( GLN[Parent GLN] ) )
)
)

Even better would be a way to do this in PowerQuery, but the only ways I've seen involve merging queries from the same table so a calculated column seems easier.
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]GLN#
[/TD]
[TD]Descr
[/TD]
[TD]Location Type
[/TD]
[TD]Parent GLN
[/TD]
[TD]*Parent Name
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Hospital A
[/TD]
[TD]Ship-To
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]Emergency
[/TD]
[TD]Deliver-To
[/TD]
[TD]1
[/TD]
[TD]Hospital A
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]OR
[/TD]
[TD]Deliver-To
[/TD]
[TD]1
[/TD]
[TD]Hospital A
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]...
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10
[/TD]
[TD]Hospital B
[/TD]
[TD]Ship-To
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11
[/TD]
[TD]Emergency
[/TD]
[TD]Deliver-To
[/TD]
[TD]10
[/TD]
[TD]Hospital B
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]...
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Last edited:

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
This would be a Power Query solution. I added a temporary index to restore the original sort order at the end.

Code:
let
    Source = Table1,
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1),
    #"Merged Queries" = Table.NestedJoin(#"Added Index",{"Parent GLN"},#"Added Index",{"GLN#"},"Parent",JoinKind.LeftOuter),
    #"Expanded Parent" = Table.ExpandTableColumn(#"Merged Queries", "Parent", {"Descr"}, {"Parent Name"}),
    #"Sorted Rows" = Table.Sort(#"Expanded Parent",{{"Index", Order.Ascending}}),
    #"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"Index"})
in
    #"Removed Columns"
 
Upvote 0
Thanks, that's doing what I wanted. But my SQL isn't as good as it could be... the Ship-To locations are getting a blank value (because there's no parent for them) but I'd like to duplicate location description if it's a Ship-To. I could add a custom column with an IF statement after the merge but wondered if there's a better way within the merge process itself?
 
Upvote 0

Forum statistics

Threads
1,223,910
Messages
6,175,318
Members
452,634
Latest member
cpostell

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top