How to create a new dynamic table when LOOKUPVALUE function doesn't work?

[Shelley]

I've used the LOOKUPVALUE function a number of times in the same table to create new columns and so I know it works. I have checked and double-checked my nomenclature and it appears to be correct. I'm thinking Power BI may not "like" this function here, because it is referencing the primary key in another table that is already referenced by another field in the current table. That is, two fields in one table both reference the same key field in the second table.

So, if this is the case, and it's just some fluke. I'm wondering if I create a separate table will I be able to make this work? (Any other ideas are welcome).

How do I create a new dynamic table from my 'Audits' table that contains the following:

* A column for the 'Audits Table'[employeerelatedprocedureID] on which the employee was audited where [employeerelatedprocedureID] <> BLANK().

* A column for the procedure name that must be looked up in the 'Procedures' table, like this: LOOKUPVALUE('Procedures'[PROCEDURES_name], 'Procedures'[Procedure ID], 'Audits'[employeerelatedprocedureid_value])

I'm not sure if this will work either, but I think it's worth a try. If I'm missing something obvious, please let me know. Ideas are appreciated. Thanks!

[Eric_Zhang · Accepted solution]

@Shelley

What is the relationship between audit and procedures? If there's proper one to many relationship, you can get the procedure name with RELATED.

Table =
ADDCOLUMNS (
    FILTER (
        'Audits Table',
        'Audits Table'[employeerelatedprocedureID] <> BLANK ()
    ),
    "pname", RELATED ( Procedures[PROCEDURES_name] )
)

If this is not your case, could you post more details, sample data for each table, relationship etc.