Return value and sum the number of matching columns

[Neiners]

We have multiple data sources that contain asset information in them. I created a correlation virtual table to combine two data source tables. I didn’t want to do a merge in Power Query so I can keep the data sources separate. The data in both data sources can contain multiple entries for each of the columns so building a relationship, using lookup values all result in errors. I used the following DAX code to combine the two tables into a virtual table.

Correlation =

DISTINCT(

   UNION(

    SELECTCOLUMNS(Table1,

     "Computer Name", Table1[ComputerName],

     "Property ID", Table1[PropertyID],

     "MAC Address", Table1[MAC Address],

     "Service Tag", Table1[Service Tag]),

    SELECTCOLUMNS(Table2,

     "Computer Name", Table2[ComputerName],

     "Property ID", Table2[PropertyID],

     "MAC Address", Table2[MAC Address],

     "Service Tag", Table2[Service Tag])

    )  

    )

What I need to do is create new columns that capture the unique IDs from each of the tables (table 1 and table 2) and a confidence matching level for each of the tables. The steps I want to perform are as follows:

1. Check if the ComputerName from table1 is in the correlation table, if so, return 1
2. Check if the PropertyID from table 1 is in the correlation table, if so, return 1
3. Check if MAC Address from table 1 is in the correlation table, if so, return 1
4. Check if Service Tag from table 1 is in the correlation table, if so, return
5. Sum the return values for a confidence percentage. NOTE: I can return 25 in the steps above to add up to 100 if that is easier.
   1. 4 = 100%
   2. 3 = 75%
   3. 2 = 50%
   4. 1= 25%
   5. 0=0%
6. Return the Unique ID from table 1 depending on a 75% or more confidence level

It doesn’t matter how many columns are created.

I have started with the following. As of now, it will only return  Table 1’s Unique ID if all 4 of the columns match. I need to do the same steps above for Table 2 so I can then show where they 100 % match each other, 75 % match, etc…This will help with data clean up. The goal is to get the unique ids from both table 1 and table 2 on the same row if the confidence rating is the same or higher.

Table1 ID = MAXX(FILTER(Table1,

    Table1[Computer Name]='Correlation'[Computer Name]

    &&

    Table1[PropertyID]='Correlation'[Property ID]

    &&

    Table1[MAC Address]=Correlation[MAC Address]

    &&

    Table1[Service Tag]=Correlation[Service Tag]

), Table1[Unique ID])

[lbendlin]

Surely one of these fields has to be a unique identifier for the asset? 

 I didn’t want to do a merge in Power Query so I can keep the data sources separate.

It would have been an append, not a merge.  Please elaborate on the reasoning.