Help with a lookup?

[rmcgrath]

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 three values:

Here is the other table...it needs to match one of those first three values to Column "Site Lookup" and return the value in "Site"

So in this example it should return "Admin" since CVF_030, CVF_030-000 and CVF_000-000 do not exist in the Site Lookup column of the second table, but CVF_000 does.

[techies · Accepted solution]

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

[techies · Accepted solution]

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 · Accepted solution]

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]

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)

[ArwaAldoud]

Hi @rmcgrath 
Please share sample data that clearly represents your issue or question. Make sure not to include any sensitive or unrelated information.

check this to upload your data
https://community.fabric.microsoft.com/t5/Power-BI-Community-Blog/How-to-provide-sample-data-in-the-...