Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Count value based on the value in other table

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,...
Need Help
  • rsbin's avatar
    rsbin
    3 years ago

    Anonymous ,

    [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's avatar
rsbin
Icon for Community Champion rankCommunity Champion
3 years ago

Anonymous ,

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,