Return value and sum the number of matching columns

Neiners — Fri, 05 Apr 2024 19:22:12 GMT

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:

Check if the ComputerName from table1 is in the correlation table, if so, return 1
Check if the PropertyID from table 1 is in the correlation table, if so, return 1
Check if MAC Address from table 1 is in the correlation table, if so, return 1
Check if Service Tag from table 1 is in the correlation table, if so, return
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%
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])

Re: Return value and sum the number of matching columns

lbendlin — Sat, 06 Apr 2024 20:06:53 GMT

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.

topic Re: Return value and sum the number of matching columns in DAX Commands and Tips

Return value and sum the number of matching columns

Re: Return value and sum the number of matching columns