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]
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: