Count rows comparing 4 columns in 2 indirectly related tables

[vnqt]

Hi ,

I have 2 tables belows

Table1

Host	DNS	DNI	DNA
AP000	10.246.10	null	10.240
AP000	10.246.10	local	10.240
AP001	10.246.10	null	10.240
AP002	10.246.10	null	10.240
AP003	10.246.10	null	10.240
AP004	10.246.10	null	10.240
AP005	10.246.10	null	10.240
AP006	10.111	cloud	10.255
AP800	10.246.10	null	10.240
AP800	10.246.11	local	10.241
AP801	10.246.12	null	10.242
AP802	10.246.13	null	10.243
AP800	10.246.14	cloud	null
AP800	10.246.15	null	10.240
AP809	10.246.16	null	10.240
AP810	10.246.17	null	10.240
AP811	10.246.18	null	10.240
AP812	10.246.19	null	10.240
AP813	10.246.20	null	10.240
AP999	10.246.21	red	10.240
AP999	10.246.22	pro	10.240
AP999	10.246.23	red	10.240
AP999	10.246.24	red	10.246

Table2

Name	Host	DNS	DNI	DNA
Name 1	AP000	10.246.10	null	10.240
Name 2	AP000	10.246.10	local	10.240
Name 3	AP001	10.246.10	null	10.240
Name 4	AP002	10.246.10	null	10.240
Name 5	AP003	10.246.10	null	10.240
Name 6	AP004	10.246.10	null	10.240
Name 7	AP005	10.246.10	null	10.240
Name 8	AP006	10.111	cloud	10.255
Name 9	AP800	10.246.10	null	10.240
Name 10	AP800	10.246.11	local	10.241
Name 11	AP801	10.246.12	null	10.242
Name 12	AP802	10.246.13	null	10.243
Name 13	AP800	10.246.14	cloud	null
Name 14	AP800	10.246.15	null	10.240
Name 15	AP809	10.246.16	null	10.240
Name 16	AP810	10.246.17	null	10.240
Name 17	AP811	10.246.18	null	10.240
Name 18	AP812	10.246.19	null	10.240
Name 19	AP813	10.246.20	null	10.240
Name 20	AP999		red	10.240
Name 21	AP999		pro	10.240
Name 22	AP999		red	10.240
Name 23	AP999		red	10.246
Name 24	AP000		null	10.240
Name 25	AP000	10.246.10	local
Name 26	AP001	10.246.10	null
Name 27	AP002	10.246.10	null
Name 28	AP003	10.246.10	null
Name 29	AP004	10.246.10	null
Name 30	AP005	10.246.10	null	10.240
Name 31	AP006	10.111	cloud	10.240
Name 32	AP800	10.246.10	null	10.240
Name 33	AP800	10.246.11	local
Name 34	AP801	10.246.12	null	10.240
Name 35	AP802	10.246.13	null	10.240
Name 36	AP800	10.246.14	cloud	10.255

I would like to add to table 1 a column : total of Names that 4 columns have same values as 4 columns in table 2. And I can have a list of name. 

Or a measure counts the names of the same Host that has the same value of DNS, DNA, DNI.

for exemple : 

Thank you in advance for your help.

[v-yadongf-msft]

Hi @vnqt ,

Please create a new column:

Total of Names = CALCULATE(COUNT('Table2'[Name]),FILTER('Table2','Table2'[Host] = 'Table1'[Host] && 'Table2'[DNS] = 'Table1'[DNS] && 'Table2'[DNI] = 'Table1'[DNI] && 'Table2'[DNA] = 'Table1'[DNA]))

The result you want:

Best regards,

Yadong Fang

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

[vnqt]

Thank you for your solution. could you please advise how to export the list of Name for each Host ? 

[v-yadongf-msft · Accepted solution]

Hi @vnqt ,

Please try following DAX:

Name list1 = CALCULATE(MAX('Table2'[Name]),FILTER('Table2','Table2'[Host] = 'Table1'[Host] && 'Table2'[DNS] = 'Table1'[DNS] && 'Table2'[DNI] = 'Table1'[DNI] && 'Table2'[DNA] = 'Table1'[DNA]))

Name list2 = 
IF('Table1'[Total of Names] > 1,CALCULATE(MIN('Table2'[Name]),FILTER('Table2','Table2'[Host] = 'Table1'[Host] && 'Table2'[DNS] = 'Table1'[DNS] && 'Table2'[DNI] = 'Table1'[DNI] && 'Table2'[DNA] = 'Table1'[DNA])))

Name list:

If you want to export, you can copy table to excel.

Best regards,

Yadong Fang

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

[vnqt]

Hi ,

We have many Hosts  that have more than 50 names. Creating a dax for each name is not a convenient solution. Could you help if there is other solution ? 

Thank you in advance. 

[v-yadongf-msft]

Hi @vnqt ,

Can you tell me what's your expected output?

Best regards,

Yadong Fang

[vnqt]

Hi ,

We have many Hosts  that have more than 50 names. Creating a dax for each name is not a convenient solution. Could you help if there is other solution ? 

Thank you in advance. 

[v-yadongf-msft]

Hi @vnqt ,

I think the method I provided is a very effective method, although a little inconvenient, please try it.

Best regards,

Yadong Fang