Create Look-up table between two un-related tables

[rpiboy_1]

I have two tables in my model each with a column that have matches between the two, however the data is not clean on either side (missing, dupes). I need to create a look-up table that resolves each side properly so that it is a clean 1:* by introducing 'blank' fillers for when data is missing. Normally I would do this all in PowerQuery, however the data sources are distinct and for various reasons I'd like to avoid an additional 'custom' semantic model to resolve this one issue and was hoping I could generate a table 'in memory' with DAX. I realize there can be potential performance pentalities for this type of approach, but I'm not terribly concerned about that risk.

Table A has many columns, but the key is 'IDNo':

IDNo	[ [...], [, ...] ]
005	<data>
006	<data>
007	<data>
007	<data>
	<data>
008	<data>

Table B is effectively identical in scope, just different (but overlapping data):

IDNo	[ [...], [, ...] ]
003	<data>
006	<data>
007	<data>
008	<data>
008	<data>
009	<data>
0	<data>

The end result I'm looking for would look like this:

IDNoA	IDNoB
-1	003
005	-1
006	006
007	007
008	008
-1	009
-1	0

the value -1 would allow me to return something like 'No Value' or 'N/A' in visuals to avoid any 'hanging' dimensions that would normally resolve as 'blank' in slicers, tables or other visuals, or take other conditional formatting steps with measures.

Any suggestions? I tried a couple of different approaches with no avail, based on the SQLBI guys.

[lbendlin · Accepted solution]

Read about NATURALLEFTOUTERJOIN function (DAX) - DAX | Microsoft Learn

You may have to apply it twice, once from each side.

[Anonymous · Accepted solution]

Your solution is great  @lbendlin , Allow me to offer another insight.
Hi,  @rpiboy_1 
Have you solved your problem? If so, can you share your solution here and mark the correct answer as a standard answer to help other members find it faster. Thank you very much for your kind cooperation!

Perhaps, you can also try the following:

I have the following two tables:
tableA:

table B:

I created a calculated table using the following DAX expression:

LookupTable = 
VAR _DistinctIDNo = UNION(
    VALUES(TableA[IDNo]),
    VALUES('TableB'[IDNo])
)
VAR _CleanDistinctIDNo = DISTINCT(_DistinctIDNo)
RETURN
ADDCOLUMNS (
    _CleanDistinctIDNo,
    "IDNoA", 
        VAR _cur= [IDNo]
        RETURN IF ( NOT _cur IN VALUES('TableA'[IDNo]),-1, CALCULATE(MAX('TableA'[IDNo]),FILTER(VALUES('TableA'[IDNo]),'TableA'[IDNo]=_cur))),
    "IDNoB", 
        VAR _cur= [IDNo]
        RETURN IF ( NOT _cur IN VALUES(TableB[IDNo]) , -1, CALCULATE(MAX('TableB'[IDNo]),FILTER(VALUES(TableB[IDNo]),'TableB'[IDNo]=_cur)))
)

Here are the results:

If you don't want others to see the IDNo column, you can hide it as follows:

I've provided the PBIX file used this time below.

How to Get Your Question Answered Quickly

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards

Jianpeng Li

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

[lbendlin · Accepted solution]

Read about NATURALLEFTOUTERJOIN function (DAX) - DAX | Microsoft Learn

You may have to apply it twice, once from each side.