Forum Discussion

rmcgrath's avatar
rmcgrath
Icon for Advocate III rankAdvocate III
1 year ago
Solved

Help with a lookup?

I am trying to make a custom column that would use these three values and if it matches any of the three in a column from a different table, it will return a value from that table.  Here are the thre...
Need Help
Show and Tell
Tips and Tricks
  • techies's avatar
    techies
    1 year ago

    Hi rmcgrath are you looking for lookupvalue function in dax ?

     

    Column =
    VAR Match1 = LOOKUPVALUE('Site Table'[Site], 'Site Table'[Site Lookup], 'First Table'[Dept Loc Code])
    VAR Match2 = LOOKUPVALUE('Site Table'[Site], 'Site Table'[Site Lookup], 'First Table'[Department Code])
    VAR Match3 = LOOKUPVALUE('Site Table'[Site], 'Site Table'[Site Lookup], 'First Table'[Location Code])

    RETURN
    COALESCE(Match1, Match2, Match3, "No Match")

  • lbendlin's avatar
    lbendlin
    1 year ago

    Please try to avoid LOOKUPVALUE. If there is a data model, use the data model. If there isn't, use TREATAS.  LOOKUPVALUE should be the very last resort.

rmcgrath's avatar
rmcgrath
Icon for Advocate III rankAdvocate III
1 year ago

Here is the code I tried and it errors out, telling me it cannot find Department Code

 

= Table.AddColumn(#"Changed Type", "Site", each let
departmentMatch = Table.SelectRows(Mapping, each [Site Lookup] = [Department Code]),
locationMatch = Table.SelectRows(Mapping, each [Site Lookup] = [Location Code]),
deptLocMatch = Table.SelectRows(Mapping, each [Site Lookup] = [Dept Loc Code]),
siteValue = if Table.RowCount(departmentMatch) > 0 then departmentMatch{0}[SITE]
else if Table.RowCount(locationMatch) > 0 then locationMatch{0}[SITE]
else if Table.RowCount(deptLocMatch) > 0 then deptLocMatch{0}[SITE]
else null
in
siteValue)