Help with lookupvalue - alternatives or advice?

[lizl]

I'm looking for an alternative to the lookupvalue function (or suggestions to get it to work).

There are two tables in my report that are connected to central "county" table that I used to filter them (relationship many to many). There are several other tables connected to this central county table as well. But the relevant table relationship looks like ActiveAgencies<=>County<=>AllAgencies.

I need to create a summary table that shows Active Agencies | Staff Name , where Active Agencies is from the ActiveAgencies table, and Staff Name is from the AllAgencies table. Both "Agencies" tables include a column of agency names. So I'd like to look up the Staff Name using the Agency Name from ActiveAgencies. However, it seems like the lookupvalue function will only work if the tables are connected, which is not possible in this case. Am I missing something with the lookupvalue function? Is there an alternative way to do this? 

[v-alq-msft · Accepted solution]

Hi, @lizl 

LOOKUPVALUE  returns the value for the row that meets all criteria specified by one or more search conditions.

LOOKUPVALUE(
    <result_columnName>,
    <search_columnName>,
    <search_value>
    [, <search2_columnName>, <search2_value>]…
    [, <alternateResult>]
)

I created data to reproduce your scenario. The pbix file is attached in the end.

Table1:

 

Table2:

You may create a calculated column or a measure like below.

Calculated column:

Result Column = LOOKUPVALUE(Table1[Value],Table1[Name],Table2[Name])

Meausre:

Result Measure = LOOKUPVALUE(Table1[Value],Table1[Name],MAX(Table2[Name]))

Result:

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[HotChilli]

"However, it seems like the lookupvalue function will only work if the tables are connected" - this is not true.

What problem are you running into?

[lizl]

When I type out the function, I was trying to use "agency name" from the ActiveAgencies table, but the column name does not come up. I've tried lookupvalue(AllAgencies[Staff Name], AllAgencies[Agency Name], but the third argument does not pull up the ActiveAgencies[Agency Name] column. I tried it with the second and third arguements flipped (i.e., lookupvalue(AllAgencies[Staff Name], ActiveAgencies[AgencyName]) in case I misunderstood the order, but I can't get it to show the active agencies table to list it as the second argument.

It's showing measure names, but I am not sure how to use a measure there. I know the parameters say that the third argument needs to be the search value and not the column but not sure how that applies here.  

Not sure if this matters, but there are names in the All table that are not in the Active table.