Counting how many rows within 'Groups' in Table01 Contain the individual Group names within Table02

[JSaunders]

Hey all,

So I have two tables:

Table01, which countain a list of Assets, with one column being a list of Groups it belongs to - Comma seperated.

Table02, which was generated as a seperate table to contain all of these independent Group names, de-duplicated and cleaned.

I would like to add another column within Table02 to show many Assets within Table01 are part of this specific group, but I can't seem to find something that works across tables (no relation) utlising Contains.

A brief view...

Table01:

Asset Name | Groups

Asset01 | Group01, Group03

Asset02 | Group02

Asset03 | Group02, Group03

Table02:

Group Name | Number of Assets

Group01 | ?

Group02 | ?

Group03 | ?

Any help would be very much appreciated!

Thanks in advance.

[amitchandak · Accepted solution]

@JSaunders , new column in table 2

countx(filter(Table1, containsstring(Table1[Group], Table2[Group])), Table1[Assets])

or

calculate(distinctCOUNT(Table1[Assets]) , filter(Table1, containsstring(Table1[Group], Table2[Group])))

[JSaunders]

Perfect! the CountX option worked like a dream.

Thanks for the help.