# LOOKUPVALUE from same table?



## macfuller (Nov 9, 2017)

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.

GLN#
Descr
Location Type
Parent GLN
*Parent Name
1
Hospital A
Ship-To
2
Emergency
Deliver-To
1
Hospital A
3
OR
Deliver-To
1
Hospital A
...
10
Hospital B
Ship-To
11
Emergency
Deliver-To
10
Hospital B
...


<tbody>

</tbody>


----------



## MarcelBeug (Nov 9, 2017)

This would be a Power Query solution. I added a temporary index to restore the original sort order at the end.


```
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"
```


----------



## macfuller (Nov 13, 2017)

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?


----------

