Measure to return multiple values from multiple tables and concatenate ? together

[Neiners]

I have 7 datasources that are not related. I have up to 5 columns (computer name, property number, serial number, mac address and IP address) that I will need to scan each datasource to see what matches and where data can be cleaned up manually or assets need to looked at because they may not be reporting back properly. I wrote this measure, where it returns all the matches from each of the needed datasources. There probably is a better way to do this, but this is working and shows the matching values.

Agent correlation(s) = 

//Agent lookups

var _computername = SELECTEDVALUE('Asset Inventory'[inventory Computer Name])

var _propertynumber = SELECTEDVALUE('Asset Inventory'[Property Number])

var _scannedmacaddress = SELECTEDVALUE('Asset Inventory'[MAC Address (Scanned)])

var _inventorymacaddress = SELECTEDVALUE('Asset Inventory'[MAC Address (Inventory)])

var _scannedipaddress = SELECTEDVALUE('Asset Inventory'[IP Address (Scanned)])

var _inventoryipaddress = SELECTEDVALUE('Asset Inventory'[IP Address (Inventory)])

//Table1 lookups

var _Table1computername = COUNTROWS(FILTER(Table1,Table1[Computer Name] = _computername))

var _Table1propertynumber = COUNTROWS(FILTER(Table1,Table1[Property Number] = _propertynumber))

var _Table1scannedmacaddress = COUNTROWS(FILTER(Table1_MACs,Table1_MACs[MAC Address] = _scannedmacaddress))

var _Table1inventorymacaddress = COUNTROWS(FILTER(Table1_MACs,Table1_MACs[MAC Address] = _inventorymacaddress))

var _Table1scannedipaddress = COUNTROWS(FILTER(Table1_IPs,Table1_IPs[IP Address] = _scannedipaddress))

var _Table1inventoryipaddress = COUNTROWS(FILTER(Table1_IPs,Table1_IPs[IP Address] = _inventoryipaddress))

//Table2 lookups

var _Table2computername = COUNTROWS(FILTER(Table2,Table2[Computer  Name] = _computername))

var _Table2scannedmacaddress = COUNTROWS(FILTER(Table2_MACs,Table2_MACs[MAC Address] = _scannedmacaddress))

var _Table2inventorymacaddress = COUNTROWS(FILTER(Table2_MACs,Table2_MACs[MAC Address] = _inventorymacaddress))

var _Table2scannedipaddress = COUNTROWS(FILTER(Table2,Table2[Last Reported IP] = _scannedipaddress))

var _Table2inventoryipaddress = COUNTROWS(FILTER(Table2,Table2[Last Reported IP] = _inventoryipaddress))

//Table3 lookups

var _Table3computername = COUNTROWS(FILTER(Table3,Table3[Computer Name] = _computername))

var _Table3scannedipaddress = COUNTROWS(FILTER(Table3,Table3[IP Address] = _scannedipaddress))

var _Table3inventoryipaddress = COUNTROWS(FILTER(Table3,Table3[IP Address] = _inventoryipaddress))

RETURN

    CONCATENATE( //Table1 ComputerName and Table1 Property Number

    CONCATENATE( //Table1 Scanned MAC Address

    CONCATENATE( //Table1 inventory MAC Address

    CONCATENATE( //Table1 Scanned IP Address

    CONCATENATE( //Table1 inventory IP Address

    CONCATENATE( //Table2 Computer Name

    CONCATENATE( //Table2 Scanned MAC Address

    CONCATENATE( //Table2 inventory MAC Address

    CONCATENATE( //Table2 Scanned IP Address

    CONCATENATE( //Table2 inventory IP Address

    CONCATENATE( //Table3 Computer Name

    CONCATENATE( //Table3 Scanned IP Address

    CONCATENATE( //Table3 inventory IP Address

//Table1

    IF(AND(

        _computername <> BLANK(),

        _Table1computername), "Table1 Computer Name - " & SELECTEDVALUE('Asset Inventory'[inventory Computer Name]) & UNICHAR(10)),

    IF(AND(

        _propertynumber <> BLANK(),

        _Table1propertynumber), "Table1 Property Number - " & SELECTEDVALUE('Asset Inventory'[Property Number]) & UNICHAR(10))),

    IF(AND(

        _scannedmacaddress <> BLANK(),

        _Table1scannedmacaddress), "Table1 Scanned MAC Address - " & SELECTEDVALUE('Asset Inventory'[MAC Address (Scanned)]) & UNICHAR(10))),

    IF(AND(

        _inventorymacaddress <> BLANK(),

        _Table1inventorymacaddress), "Table1/inventory MAC Address - " & SELECTEDVALUE('Asset Inventory'[MAC Address (Inventory)]) & UNICHAR(10))),

    IF(AND(

        _scannedipaddress <> BLANK(),

        _Table1scannedipaddress), "Table1 Scanned IP Address - " & SELECTEDVALUE('Asset Inventory'[IP Address (Scanned)]) & UNICHAR(10))),

    IF(AND(

        _inventoryipaddress <> BLANK(),

        _Table1inventoryipaddress), "Table1/inventory IP Address - " & SELECTEDVALUE('Asset Inventory'[IP Address (Inventory)]) & UNICHAR(10))),

//Table2

    IF(AND(

        _computername <> BLANK(),

        _Table2computername), "Table2 Computer Name - " & SELECTEDVALUE('Asset Inventory'[inventory Computer Name]) & UNICHAR(10))),

    IF(AND(

        _scannedmacaddress <> BLANK(),

        _Table2scannedmacaddress), "Table2 Scanned MAC Address - " & SELECTEDVALUE('Asset Inventory'[MAC Address (Scanned)]) & UNICHAR(10))),

    IF(AND(

        _inventorymacaddress <> BLANK(),

        _Table2inventorymacaddress), "Table2/inventory MAC Address - " & SELECTEDVALUE('Asset Inventory'[MAC Address (Inventory)]) & UNICHAR(10))),

    IF(AND(

        _scannedipaddress <> BLANK(),

        _Table2scannedipaddress), "Table2 Scanned IP Address - " & SELECTEDVALUE('Asset Inventory'[IP Address (Scanned)]) & UNICHAR(10))),

    IF(AND(

        _inventoryipaddress <> BLANK(),

        _Table2inventoryipaddress), "Table2/inventory IP Address - " & SELECTEDVALUE('Asset Inventory'[IP Address (Inventory)]) & UNICHAR(10))),

//Table3

    IF(AND(

        _computername <> BLANK(),

        _Table3computername), "Table3 Computer Name - " & SELECTEDVALUE('Asset Inventory'[inventory Computer Name]) & UNICHAR(10))),

    IF(AND(

        _scannedipaddress <> BLANK(),

        _Table3scannedipaddress), "Table3 Scanned IP Address - " & SELECTEDVALUE('Asset Inventory'[IP Address (Scanned)]) & UNICHAR(10))),

    IF(AND(

        _inventoryipaddress <> BLANK(),

        _Table3inventoryipaddress), "Table3/inventory IP Address - " & SELECTEDVALUE('Asset Inventory'[IP Address (Inventory)]) & UNICHAR(10)))

Then wrote the following measure to take those matching fields, and return another column from those matching datasources. This is working UNLESS it finds more than one match. 

Table1 Report Date (Inventory Computer Name) = 

//Inventory lookups

var _computername = SELECTEDVALUE('Asset Inventory'[inventory Computer Name])

var _propertynumber = SELECTEDVALUE('Asset Inventory'[Property Number])

var _scannedmacaddress = SELECTEDVALUE('Asset Inventory'[MAC Address (Scanned)])

var _inventorymacaddress = SELECTEDVALUE('Asset Inventory'[MAC Address (inventory)])

var _scannedipaddress = SELECTEDVALUE('Asset Inventory'[IP Address (Scanned)])

var _inventoryipaddress = SELECTEDVALUE('Asset Inventory'[IP Address (inventory)])

//Table1 lookups

var _Table1computername = COUNTROWS(FILTER(Table1,Table1[Computer Name] = _computername))

var _Table1propertynumber = COUNTROWS(FILTER(Table1,Table1[Property Number] = _propertynumber))

var _Table1scannedmacaddress = COUNTROWS(FILTER(Table1_MACs,Table1_MACs[MAC Address] = _scannedmacaddress))

var _Table1inventorymacaddress = COUNTROWS(FILTER(Table1_MACs,Table1_MACs[MAC Address] = _inventorymacaddress))

var _Table1scannedipaddress = COUNTROWS(FILTER(Table1_IPs,Table1_IPs[IP Address] = _scannedipaddress))

var _Table1inventoryipaddress = COUNTROWS(FILTER(Table1_IPs,Table1_IPs[IP Address] = _inventoryipaddress))

RETURN

//Table1

    IF(AND(

        _computername <> BLANK(),

        _Table1computername), "Computer Name - " & LOOKUPVALUE(Table1[Last Report Time],Table1[Computer Name],_computername))

I have tried using filter and I am getting the same thing.   Eventually, I want to concatenate the distinct returned values together similar to the 1st measure. I have started with using this but have ran into a problem when more results are found which is why some of the code is commented out.

Table1 Report Date (Inventory Computer Name, Property Number, MAC Address) = 

//Inventory lookups

var _computername = SELECTEDVALUE('Asset Inventory'[inventory Computer Name])

var _propertynumber = SELECTEDVALUE('Asset Inventory'[Property Number])

var _scannedmacaddress = SELECTEDVALUE('Asset Inventory'[MAC Address (Scanned)])

var _inventorymacaddress = SELECTEDVALUE('Asset Inventory'[MAC Address (inventory)])

var _scannedipaddress = SELECTEDVALUE('Asset Inventory'[IP Address (Scanned)])

var _inventoryipaddress = SELECTEDVALUE('Asset Inventory'[IP Address (inventory)])

//Table1 lookups

var _Table1computername = COUNTROWS(FILTER(Table1,Table1[Computer Name] = _computername))

var _Table1propertynumber = COUNTROWS(FILTER(Table1,Table1[Property Number] = _propertynumber))

var _Table1scannedmacaddress = COUNTROWS(FILTER(Table1_MACs,Table1_MACs[MAC Address] = _scannedmacaddress))

var _Table1inventorymacaddress = COUNTROWS(FILTER(Table1_MACs,Table1_MACs[MAC Address] = _inventorymacaddress))

var _Table1scannedipaddress = COUNTROWS(FILTER(Table1_IPs,Table1_IPs[IP Address] = _scannedipaddress))

var _Table1inventoryipaddress = COUNTROWS(FILTER(Table1_IPs,Table1_IPs[IP Address] = _inventoryipaddress))

RETURN

    CONCATENATE( //Table1 ComputerName and Table1 Property Number

    CONCATENATE( //Table1 Scanned MAC Address

    //CONCATENATE( //Table1 inventory MAC Address

    //CONCATENATE( //Table1 Scanned IP Address

    //CONCATENATE( //Table1 inventory IP Address */

//Table1

    IF(AND(

        _computername <> BLANK(),

        _Table1computername), "Computer Name - " & LOOKUPVALUE(Table1[Last Report Time],Table1[Computer Name],_computername) & UNICHAR(10)),

    IF(AND(

        _propertynumber <> BLANK(),

        _Table1propertynumber), "Property Number - " & LOOKUPVALUE(Table1[Last Report Time], Table1[Property Number],_propertynumber) & UNICHAR(10))),

    IF(AND(

        _scannedmacaddress <> BLANK(),

        _Table1scannedmacaddress), "Table1 Scanned MAC Address - " & LOOKUPVALUE(Table1_MACs[Last Report Time],Table1_MACs[MAC Address],_scannedmacaddress) & UNICHAR(10)))

    /*IF(AND(

        _inventorymacaddress <> BLANK(),

        _Table1inventorymacaddress), "Table1/inventory MAC Address - " & SELECTEDVALUE('Asset Inventory'[MAC Address (inventory)]) & UNICHAR(10))),

    IF(AND(

        _scannedipaddress <> BLANK(),

        _Table1scannedipaddress), "Table1 Scanned IP Address - " & SELECTEDVALUE('Asset Inventory'[IP Address (Scanned)]) & UNICHAR(10))),

    IF(AND(

        _inventoryipaddress <> BLANK(),

        _Table1inventoryipaddress), "Table1/inventory IP Address - " & SELECTEDVALUE('Asset Inventory'[IP Address (inventory)]) & UNICHAR(10)))))

*/

 Any help would be greatly appreciated and hopefully this is possible.

[v-heq-msft]

Hi @Neiners ,
Based on your description, it looks like you want to create a DAX metric in Power BI that returns multiple values from multiple tables. This can be a bit tricky because DAX metrics typically return a single value. However, you can accomplish this by using a combination of DAX functions.
You can use variables to store values, and then you can use the CONCATENATE function to combine values from the same variables. Perhaps you could also use '&' to concatenate variables.
If you want to return multiple rows of data, you can use SUMMARIZR,SELECTEDCOLUMNS to create a new table.
You can check out these documents for more
Solved: How to get a measure to return multiple rows for e... - Microsoft Fabric Community
Solved: Measure To Return Value based on multiple criteria... - Microsoft Fabric Community
Solved: Measure to return multiple values - Microsoft Fabric Community
CONCATENATE function (DAX) - DAX | Microsoft Learn

Best regards,
Albert He

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