Count value based on the value in other table

[vnqt]

Hello,

I have 2 tables as below : 

Table1

Category	DNA1	DNA2	DNA3	DNA4	DNA5
AP0001	10.10.200, 10.10.200
AP0002	10.10.200, 10.10.200
AP0003	10.10.200, 10.10.200
AP0004	10.10.200, 10.10.201
AP0005	10.10.200, 10.10.202
AP0006	10.10.200, 10.10.200	10.10.200, 10.10.201	10.10.200, 10.10.202
AP0007	10.10.11, 10.10.12	10.10.11, 10.10.13	10.10.11, 10.10.14	10.10.11, 10.10.15	10.10.11, 10.10.16
AP0008	10.10.200, 10.10.200
AP0009	10.10.200, 10.10.200

Table2

Category	Host	DNA
AP0001	Host 1	10.10.200, 10.10.200
AP0001	Host 14	10.10.200, 10.10.201
AP0001	Host 27	10.10.200, 10.10.200
AP0002	Host 2	10.10.200, 10.10.200
AP0002	Host 15	10.10.200, 10.10.200
AP0002	Host 28	10.10.200, 10.10.200
AP0003	Host 3	10.10.200, 10.10.200
AP0003	Host 16	10.10.200, 10.10.200
AP0003	Host 29	10.10.200, 10.10.200
AP0004	Host 4	10.10.200, 10.10.200
AP0004	Host 17	10.10.200, 10.10.201
AP0004	Host 30	10.10.200, 10.10.201
AP0004	Host 10	10.10.200, 10.10.202
AP0004	Host 23	10.10.200, 10.10.203
AP0004	Host 36	10.10.200, 10.10.204
AP0005	Host 5	10.10.200, 10.10.205
AP0005	Host 18	10.10.200, 10.10.206
AP0005	Host 31	10.10.200, 10.10.207
AP0005	Host 11	10.10.200, 10.10.208
AP0005	Host 24	10.10.200, 10.10.209
AP0005	Host 37	10.10.200, 10.10.210
AP0006	Host 6	10.10.200, 10.10.211
AP0006	Host 19	10.10.200, 10.10.212
AP0006	Host 32	10.10.200, 10.10.213
AP0006	Host 12	10.10.200, 10.10.214
AP0006	Host 25	10.10.200, 10.10.215
AP0006	Host 38	10.10.200, 10.10.201
AP0007	Host 7	10.10.11, 10.10.12
AP0007	Host 20	10.10.11, 10.10.12
AP0007	Host 33	10.10.200, 10.10.200
AP0007	Host 13	10.10.200, 10.10.200
AP0007	Host 26	10.10.11, 10.10.14
AP0007	Host 39	10.10.11, 10.10.16
AP0008	Host 8	10.10.200, 10.10.200
AP0008	Host 21	10.10.11, 10.10.16
AP0008	Host 34	10.10.200, 10.10.207
AP0009	Host 9	10.10.200, 10.10.208
AP0009	Host 22	10.10.200, 10.10.209
AP0009	Host 35	10.10.200, 10.10.200

Could you please advise the Dax to distinctcount the hosts (Table2) (in the same Category) which have the value DNA = one of DNA in the table 1 

The 2 tables have the relation by Category Column. 

Thank you in advance. 

[v-shex-msft]

Hi @vnqt,

Did the above suggestions help with your scenario? if that is the case, you can consider Kudo or Accept the helpful suggestions to help others who faced similar requirements.

If these also don't help, please share more detailed information to help us clarify your scenario to test.

How to Get Your Question Answered Quickly 

Regards,

Xiaoxin Sheng

[vnqt]

Hi @rsbin 

The table1 is the reference table, we can't duplicate the value of Category in this column. Hope you can suggest other solution ? Many thanks. 

[rsbin]

@vnqt ,

OK here goes my second attempt:

DNAValidation2 = 
VAR _CurrentCategory = DNATable2[Category]
VAR _DNA = SUMMARIZE( FILTER( DNATable1_Pivoted, DNATable1_Pivoted[Category] = _CurrentCategory), 
                      "DNAValues", MAX(DNATable1[DNA] ) & "-" & MAX(DNATable1[DNA1] ) & "-" & MAX(DNATable1[DNA2] ) & MAX(DNATable1[DNA3]) & MAX(DNATable1[DNA4] ))

RETURN
   IF( CONTAINSSTRING(_DNA, [DNA] ), 1, 0 )

Hoping this alternate solution will work for you.

Regards,

[vnqt]

@rsbin 

You lost me. Could you please share the Pbix ? The table1 should not be pivoted or duplicate the row. I don't really understand your solution, could you give more details ? 

Thank you in advance.

[rsbin · Accepted solution]

@vnqt ,

[DNAValidation2] is a Calculated Column :

Category	Host	DNA	DNAValidation2
AP0001	Host 1	10.10.200, 10.10.200	1
AP0001	Host 14	10.10.200, 10.10.201	0
AP0001	Host 27	10.10.200, 10.10.200	1
AP0002	Host 2	10.10.200, 10.10.200	1

First, creating a Temp Variable to determine the Category for the particular record (i.e AP0001)

The nex VAR is creating a virtual table.  In your Table 1, I am filtering for the Category of the first VAR.

Then creating a one column table (think a cell in Excel) that concatenates all of the DNA Values for that Category.  

Category	DNA	DNA1	DNA2	DNA3	DNA4
AP0001	10.10.200, 10.10.200

AP0001 only has 1 value so the result of the Concatenation is "10.10.200, 10.10.200".

Then I am searching the DNA Value in your Table 2 to see if it is contained in the concatenated value. DNA in Table 2 is "10.10.200, 10.10.200" and hence is contained in the temp value _DNA

If it is, I flag it as a 1, if not 0.

The next DNA value for AP001 (Host 14) is "10.10.200, 10.10.201".  This value is not contained within the _DNA Temp value, hence the flag =0 and Host 14 should not be counted.

Hope this explanation provides the guidance you are looking for.

[rsbin]

@vnqt ,

Hoping this solution or something close to it will work.

First, in Power Query, Unpivot your Table1 so it looks like this.

CategoryDNACategoryDNA

AP0001	DNA	10.10.200, 10.10.200
AP0001	DNA1
AP0001	DNA2
AP0001	DNA3
AP0001	DNA4
AP0002	DNA	10.10.200, 10.10.200
AP0002	DNA1
AP0002	DNA2
AP0002	DNA3
AP0002	DNA4
AP0003	DNA	10.10.200, 10.10.200
AP0003	DNA1
AP0003	DNA2
AP0003	DNA3
AP0003	DNA4
AP0004	DNA	10.10.200, 10.10.201
AP0004	DNA1
AP0004	DNA2
AP0004	DNA3
AP0004	DNA4

Then in Table2, create a Calculated Column (a number of ways to do this) such as this:

DNAValidation = 
VAR _CurrentCategory = DNATable2[Category]
VAR _DNA = SUMMARIZE( FILTER( DNATable1, DNATable1[Category] = _CurrentCategory), DNATable1[DNA] ) //this creates a virtual table

RETURN
   IF( [DNA] IN _DNA, 1, 0 )  //this checks to see if your DNA is in the virtual table

Category  Host         DNA                        DNAValidation

AP0001	Host 1	10.10.200, 10.10.200	1
AP0001	Host 14	10.10.200, 10.10.201	0
AP0001	Host 27	10.10.200, 10.10.200	1
AP0002	Host 2	10.10.200, 10.10.200	1
AP0002	Host 15	10.10.200, 10.10.200	1
AP0002	Host 28	10.10.200, 10.10.200	1
AP0003	Host 3	10.10.200, 10.10.200	1
AP0003	Host 16	10.10.200, 10.10.200	1
AP0003	Host 29	10.10.200, 10.10.200	1
AP0004	Host 4	10.10.200, 10.10.200	0
AP0004	Host 17	10.10.200, 10.10.201	1
AP0004	Host 30	10.10.200, 10.10.201	1
AP0004	Host 10	10.10.200, 10.10.202	0
AP0004	Host 23	10.10.200, 10.10.203	0
AP0004	Host 36	10.10.200, 10.10.204	0
AP0005	Host 11	10.10.200, 10.10.208	0
AP0005	Host 24	10.10.200, 10.10.209	0
AP0005	Host 37	10.10.200, 10.10.210	0
AP0006	Host 6	10.10.200, 10.10.211	0
AP0006	Host 19	10.10.200, 10.10.212	0
AP0006	Host 32	10.10.200, 10.10.213	0
AP0006	Host 12	10.10.200, 10.10.214	0
AP0006	Host 25	10.10.200, 10.10.215	0
AP0006	Host 38	10.10.200, 10.10.201	1
AP0007	Host 7	10.10.11, 10.10.12	1
AP0007	Host 20	10.10.11, 10.10.12	1
AP0007	Host 33	10.10.200, 10.10.200	0
AP0007	Host 13	10.10.200, 10.10.200	0
AP0007	Host 26	10.10.11, 10.10.14	1
AP0007	Host 39	10.10.11, 10.10.16	1
AP0008	Host 8	10.10.200, 10.10.200	1
AP0008	Host 21	10.10.11, 10.10.16	0
AP0008	Host 34	10.10.200, 10.10.207	0
AP0009	Host 9	10.10.200, 10.10.208	0
AP0009	Host 22	10.10.200, 10.10.209	0
AP0009	Host 35	10.10.200, 10.10.200	1

I hope these results are accurate.

Then I think all you need to do is a SUM on the Validation to get the Number of Hosts.

Anxious to know if you can get this to work.

Regards,