Use Value from One Field as a Proxy input in another field to search for yet a different field value

[REWright]

I need a DAX query to perform the following complicated process.  Creater a new field "X".

First, use Field A in Table 1 to search for a value in Field B in Table 2.

If Field B value, then return that value as "X".  End process.

However, if Field B is blank/empty, then...

Use Field A, Field C and Field D in Table 1 to identify distinct Field E in Table 1.

Take value from Field E identified immediately above and use it as a 'proxy' value to search Field W in Table 2.

If proxy value exists in Field W in Table 2 then get the value in Field B in Table 2 and use that as "X".

If still blank/empty, return blank for field "X".

[REWright]

Still hoping for assistance on this open item.  Appreciate any time, expertise and guidance that anyone is able to provide.

[foodd]

This was composed on a phone and is untested.

X =
VAR SearchValue = 'Table 1'[Field A]
VAR ProxyValue =
    IF (
        NOT ( ISBLANK ( 'Table 1'[Field B] ) ),
        'Table 1'[Field B],
        LOOKUPVALUE (
            'Table 1'[Field E],
            'Table 1'[Field A], SearchValue,
            'Table 1'[Field C], 'Table 1'[Field C],
            'Table 1'[Field D], 'Table 1'[Field D]
        )
    )
RETURN
    IF (
        NOT ( ISBLANK ( ProxyValue ) ),
        LOOKUPVALUE ( 'Table 2'[Field B], 'Table 2'[Field W], ProxyValue ),
        BLANK ()
    )

[REWright]

Thank you @foodd I've tried to create the field using the above DAX.  However I receive an Error stating "A single value for Field B in Table 2 cannot be determined.  This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result."

Perhaps there should be more to the initial part of the process.

Based on Field A (Table 1), Field C (Table 1) and Field D (Table 1) for Field Date (Table 3), return value for Field B (Table 2).  That should identify the unique or specific Field B in the first part of the process/evaluation criteria.  This is what was attempted to assist in the second part of the process to identify Field E.

[foodd]

As I mentioned, unfortunately, I am unable to test this.  So with that, please add your work-in-progress Power BI Desktop file so that members of the forum are able to determine the state of the model, measures, and relationships, and debug any DAX added.  This is one of the first requests of anyone

when they add a post to the forum.

Please provide your work-in-progress Power BI Desktop file (with sensitive information removed) that covers your issue or question completely in a usable format (not as a screenshot).

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

Please show the expected outcome based on the sample data you provided.

https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

This allows members of the Forum to assess the state of the model, report layer, relationships, and any DAX applied.

[REWright]

Please see link to pbix file.  It is a condensed version of the full report with the pertinent tables and fields involved in this specific challenge. 

The expected outcome is a value from InventJournalId field, if one exists, populating into the report Table for the specific line.  If no content exists in the InventJournalId field for that specific line then it remains blank.

Please let me know of additional questions. 

Appreciate the time, interest and any assistance.  

CCC Test Report_Journal.pbix