Concatenate table filters together and get a list of distinct values

[Neiners]

Hi-

I have multiple tables pertaining to assets. In this example, Table 1 only contains the computer name and what I would like to do is use the computer name to lookup across the other tables and return any mac addresses that belong to that asset in a calculated column and then bring back all the distinct mac addresses. The problem that I am running into is one table may have 2 mac addresses associated with that asset, another table may have 4 mac addresses associated with it, another table may only have one mac address that is associated with it. The same mac address can be found across the different tables. If a table has multiple mac addresses associated with that computer name, the mac addresses are being seperated by a delimiter. When trying to get a list of distinct values, it sees the delimiter as part of the value and so each mac address does not appear distinct.  Here is the code that I have:

MAC Address Lookup =
var _computername = Table1[Computer Name]

//Table2
var _Table2computername = COUNTROWS(FILTER(C_Table2macs, C_Table2macs[Computer Name] = _computername))
var _Table2computernamereturned =
IF(_computername = BLANK(), BLANK(),
IF(_Table2computername <> BLANK(),
CONCATENATEX(FILTER(C_Table2macs,
C_Table2macs[Computer Name] = _computername), C_Table2macs[MAC Address], ",")))

//Table3
var _Table3computername = COUNTROWS(FILTER(C_Table3macs,C_Table3macs[Computer Name] = _computername))
var _Table3computernamereturned =
IF(_computername = BLANK(), BLANK(),
IF(_Table3computername <> BLANK(),
CONCATENATEX(FILTER(C_Table3macs,
C_Table3macs[Computer Name] = _computername), C_Table3macs[MAC Address], ",")))

//Table4
var _Table4computername = COUNTROWS(FILTER(C_Table4macs,C_Table4macs[Endpoint Name] = _computername))
var _Table4computernamereturned =
IF(_computername = BLANK(), BLANK(),
IF(_Table4computername <> BLANK(),
CONCATENATEX(FILTER(C_Table4macs,
C_Table4macs[Endpoint Name] = _computername), C_Table4macs[MAC Address], "," )))

//Table5
var _Table5computername = COUNTROWS(FILTER(Table5macs,Table5macs[Computer Name] = _computername))
var _Table5computernamereturned =
IF(_computername = BLANK(), BLANK(),
IF(_Table5computername <> BLANK(),
CONCATENATEX(FILTER(Table5IPSummary,
Table5macs[Computer Name] = _computername), Table5macs[MAC Address], ",")))

//table
var _table =
FILTER(
{
_Table2computernamereturned,
_Table3computernamereturned,
_Table4computernamereturned,
_Table5computernamereturned
},
NOT ISBLANK([Value])
)

var _filtertable =
CONCATENATEX(DISTINCT(_table), [Value], UNICHAR(10))
var _substitute = SUBSTITUTE(_filtertable, "," , "|")

RETURN
_filtertable

The column returns:

mac 1, mac 2

mac 1, mac 2, mac 3, mac 4, mac 5

mac 1, mac 6

mac 1

I prefer UNICHAR (10) as the delimiter because I find it easier to read in the reports. If I use UNICHAR(10) as the delimiter  for each _(table)computernamereturned the result will read:

mac 1

mac 2

mac 1

mac 2

mac 3

mac 4

mac 5

mac 1

mac 6

mac 1

What I would like to return is:

mac 1

mac 2

mac 3

mac 4

mac 5

mac 6

[johnbasha33]

 
Hi, You need to achieve two things in your DAX measure:

1. Extract all MAC addresses from multiple tables while handling the delimiter issue.

2. Get distinct MAC addresses across all tables and return them as a list with UNICHAR(10) as a separator.

   Solution

   Instead of using CONCATENATEX with a , delimiter, which treats the entire string as one value, you should:

   • Split the MAC addresses by the delimiter (,)

   • Normalize them into individual values

   • Return distinct values using UNION and DISTINCT

     Updated DAX:
     

     MAC Address Lookup =
     VAR _computername = Table1[Computer Name]

     -- Collect MAC addresses from each table
     VAR _Table2MACs =
     FILTER(C_Table2macs, C_Table2macs[Computer Name] = _computername)

     VAR _Table3MACs =
     FILTER(C_Table3macs, C_Table3macs[Computer Name] = _computername)

     VAR _Table4MACs =
     FILTER(C_Table4macs, C_Table4macs[Endpoint Name] = _computername)

     VAR _Table5MACs =
     FILTER(Table5macs, Table5macs[Computer Name] = _computername)

     -- Create a unified table of MAC addresses
     VAR _AllMACs =
     UNION(
     SELECTCOLUMNS(_Table2MACs, "MAC Address", C_Table2macs[MAC Address]),
     SELECTCOLUMNS(_Table3MACs, "MAC Address", C_Table3macs[MAC Address]),
     SELECTCOLUMNS(_Table4MACs, "MAC Address", C_Table4macs[MAC Address]),
     SELECTCOLUMNS(_Table5MACs, "MAC Address", Table5macs[MAC Address])
     )

     -- Remove duplicates
     VAR _DistinctMACs = DISTINCT(_AllMACs)

     -- Convert into a readable list with line breaks
     RETURN CONCATENATEX(_DistinctMACs, [MAC Address], UNICHAR(10))
     
     Did I answer your question? Mark my post as a solution! Appreciate your Kudos !!

      

@Neiners

[Neiners]

This works great, what would the formula be if the computer name has multiple values for the lookup that is seperated by the unichar(10) delimiter?  More than likely, this won't happen with the computer name but it will happen when I need to do the reverse and look up by the MAC Addresses. So at the beginning of the formula instead of looking up MAC Addresses it would be looking up Computer Names among the other tables.

Computer Name Lookup = 

var _macaddress = SUBSTITUTE(TABLE1(MAC Address), UNICHAR(10), "|")

//Collect Computer Name from each table

var _table2computername = 

IF(SELECTEDVALUE(Table1[MAC Address] <> BLANK(),

// var _txt = VALUES(Table1[MAC Address])
// var _txtPath = SUBSTITUTE(_txt, UNICHAR(10), "|")
var _txtPathlen = PATHLENGTH(_macaddress)
var _pathIndexes = GENERATESERIES(1, _txtPathlen, 1)
var _pathIndexesLookups =
GENERATE(_pathIndexes,
CALCULATETABLE(
VALUES(Table2[Computer Name]),
TREATAS( { PATHITEM(_txtPath, [Value], TEXT) }, Table2[MAC Address])
)
)
RETURN
CONCATENATEX(_pathIndexesLookups, Table2[Computer Name], UNICHAR(10)))

I want to do this for the remaining tables but then union the results together with UNICHAR(10) like you provided in the previous example.

[v-sgandrathi]

Hi @Neiners,

Try this Measure:

MultiComp =

VAR _computerList = [Computer Name Input] -- Assume this is a string with UNICHAR(10) separated names

VAR _computerTable = 
    GENERATE(
        SERIES(1, PATHLENGTH(SUBSTITUTE(_computerList, UNICHAR(10), "|")) + 1, 1),
        VAR _index = [Value]
        RETURN ROW("Computer Name", PATHITEM(SUBSTITUTE(_computerList, UNICHAR(10), "|"), _index))
    )

-- Collect MAC addresses from each table based on the Computer Names

VAR _Table2MACs =
    FILTER(
        C_Table2macs,
        C_Table2macs[Computer Name] IN SELECTCOLUMNS(_computerTable, "Computer Name", [Computer Name])
    )

VAR _Table3MACs =
    FILTER(
        C_Table3macs,
        C_Table3macs[Computer Name] IN SELECTCOLUMNS(_computerTable, "Computer Name", [Computer Name])
    )

VAR _Table4MACs =
    FILTER(
        C_Table4macs,
        C_Table4macs[Endpoint Name] IN SELECTCOLUMNS(_computerTable, "Computer Name", [Computer Name])
    )

VAR _Table5MACs =
    FILTER(
        Table5macs,
        Table5macs[Computer Name] IN SELECTCOLUMNS(_computerTable, "Computer Name", [Computer Name])
    )

-- Create a unified table for MAC addresses
VAR _AllMACs =
    UNION(
        SELECTCOLUMNS(_Table2MACs, "MAC Address", C_Table2macs[MAC Address]),
        SELECTCOLUMNS(_Table3MACs, "MAC Address", C_Table3macs[MAC Address]),
        SELECTCOLUMNS(_Table4MACs, "MAC Address", C_Table4macs[MAC Address]),
        SELECTCOLUMNS(_Table5MACs, "MAC Address", Table5macs[MAC Address])
    )

VAR _DistinctMACs = DISTINCT(_AllMACs)

RETURN
    CONCATENATEX(_DistinctMACs, [MAC Address], UNICHAR(10))

If this solution worked for you, kindly mark it as Accept as Solution and feel free to give a Kudos, it would be much appreciated!

Thank you.

[Neiners]

I am almost there. Is there any way to exclude where the [value] is blank to not return the mac addresses that match a blank computer name?I think I have tried every combination of NOT ISBLANK or <> "" and I just can't get it to work. Basically, I want to exclude filtering on a blank value.

[v-sgandrathi]

Hi @Neiners,

Yes you can filter the null values by adding another table function like filter or calculated table on top of _computerTable if you want to remove the nulls and then pass the new table onto other tables where Union happens as a reference table

Hope this helps! If this solution worked for you, kindly mark it as Accept as Solution and feel free to give a Kudos, it would be much appreciated!

Thank you.

[v-sgandrathi]

Hi @Neiners,

May I ask if you have gotten this issue resolved?

If it is solved, please mark the helpful reply or share your solution and accept it as solution, it will be helpful for other members of the community who have similar problems as yours to solve it faster.

Thank you.

[v-sgandrathi]

Hi @Neiners,

I'm glad to hear that you found a solution and resolved the query. Thank you for sharing it here!

Please mark your response as the accepted solution to help others in the community find it easily.

Thank you for being a part of the Microsoft Community Forum!

Thank you.