Replace a value with data from a different table if blank

[Anonymous]

Hello!

I have a requirement to replace any blank values in a column with a different value.  While this can be done with an IF statement in the same table, the replacement value is in a different table.  I've tried using RELATED but it isn't working, getting the error "A single value or column 'value' in table 'table' cannot be determined" even though Intellisense recognizes the table.  

Doing some research I believe that RELATED only works when the tables are directly related.  However, in this situation the data is split between four individually linked tables (below).  Unfortunately I cannot change how these relationships are laid out.  

Placing table 1 and table 4 values into the same visual table works perfectly, so the tables are setup correctly.  Here is a visual of what I want to happen: 

Table 1[value 1]	Table 4[value4]
Eggs	Store 1
(blank value, so it should display Store 2)	Store 2
Milk	Store 3

Is there any way to make this requirement happen?  Thanks!  

DAX IF Statement: 

Swap Value = IF('table1'[value1] = "", RELATED(table4[value4]), 'table1'[value1])

Table Structure: 
Table 1 (if blank) --> Table 2 --> Table 3 --> Table 4 (use this value) 

Table 1 -> Table 2: Many to Many, Cross Filter Direction: Both
Table 2 -> Table 3: Many to One, Cross Filter Direction: Both 
Table 3 -> Table 4: Many to One, Cross Filter Direction: Both 

[Anonymous · Accepted solution]

Figured it out!  Added this to a new column based on the test data: 

Replace =

IF('Table1'[Table 1 "names"] = "",

    (

        VAR Test1 = LOOKUPVALUE(Table2[Location 1 keys], Table2[Nameskey], Table1[Names keys])

        VAR Test2 = LOOKUPVALUE(Table3[Location 2 keys], Table3[Location 1 keys], Test1)

        VAR Test3 = LOOKUPVALUE(Table4[Table 4 "account"], Table4[Location 2 keys], Test2)

        Return Test3

    ),

    Table1[Table 1 "names"]

)

[v-yalanwu-msft]

Hi, @Anonymous ;

Since the data is sensitive, you can create some fake data, and the results you want to output.

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hello @v-yalanwu-msft ,

I created a sample report with sample data.  I couldn't attach the report directly, but found an upload service suggested on this forum.  

Below is a screenshot from the report.  The end goal is to replace that blank value in the 'Table 1"names"' column with "AccountValue2" from the 'Table 4'account' column.  All of the relationships are the same as the real data.  However, there are several other tables in the live data connected to these tables, which is why I can't modify the relationships (the data is imported from Dynamics 365).  

Upload files for free - Table Value Replacement Fake Data.zip - ufile.io

[v-yalanwu-msft]

Hi, @Anonymous ;

RELATED is generally used in one-to-one or one-to-many relationships, so your example is not suitable for using the RELED function, maybe you can use the LOOKUPVALUE() function,
Since I can't restore your model relationships, I don't know much about the specific connection relationship fields of your 4 tables; So can't give a precise solution, can you share a simple small example of four tables? And what are the columns of the four table join relationships?

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hello @v-yalanwu-msft !

Thank you for the help!  To answer your question, here are how the tables are linked together.  Because the data is sensitive I can't give specific examples, so if you need more info than this just let me know and I'll try to clarify. 

The data I'm trying to share are both text based.  Table 1 contains names, table 4 contains locations.  

Table 1 is related to table 2 through a unique name ID (IE: 12345 for John).  These show up multiple times in each table.  

Talbe 2 is related to table 3 through a unique location ID.  This shows up multiple times in table 2

Table 3 is related to table 4 through a unique account ID.  This shows up multiple times in table 3.