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

CategoryDNA1DNA2DNA3DNA4DNA5
AP000110.10.200, 10.10.200    
AP000210.10.200, 10.10.200    
AP000310.10.200, 10.10.200    
AP000410.10.200, 10.10.201    
AP000510.10.200, 10.10.202    
AP000610.10.200, 10.10.20010.10.200, 10.10.20110.10.200, 10.10.202  
AP000710.10.11, 10.10.1210.10.11, 10.10.1310.10.11, 10.10.1410.10.11, 10.10.1510.10.11, 10.10.16
AP000810.10.200, 10.10.200    
AP000910.10.200, 10.10.200    

 

Table2

CategoryHostDNA
AP0001Host 110.10.200, 10.10.200
AP0001Host 1410.10.200, 10.10.201
AP0001Host 2710.10.200, 10.10.200
AP0002Host 210.10.200, 10.10.200
AP0002Host 1510.10.200, 10.10.200
AP0002Host 2810.10.200, 10.10.200
AP0003Host 310.10.200, 10.10.200
AP0003Host 1610.10.200, 10.10.200
AP0003Host 2910.10.200, 10.10.200
AP0004Host 410.10.200, 10.10.200
AP0004Host 1710.10.200, 10.10.201
AP0004Host 3010.10.200, 10.10.201
AP0004Host 1010.10.200, 10.10.202
AP0004Host 2310.10.200, 10.10.203
AP0004Host 3610.10.200, 10.10.204
AP0005Host 510.10.200, 10.10.205
AP0005Host 1810.10.200, 10.10.206
AP0005Host 3110.10.200, 10.10.207
AP0005Host 1110.10.200, 10.10.208
AP0005Host 2410.10.200, 10.10.209
AP0005Host 3710.10.200, 10.10.210
AP0006Host 610.10.200, 10.10.211
AP0006Host 1910.10.200, 10.10.212
AP0006Host 3210.10.200, 10.10.213
AP0006Host 1210.10.200, 10.10.214
AP0006Host 2510.10.200, 10.10.215
AP0006Host 3810.10.200, 10.10.201
AP0007Host 710.10.11, 10.10.12
AP0007Host 2010.10.11, 10.10.12
AP0007Host 3310.10.200, 10.10.200
AP0007Host 1310.10.200, 10.10.200
AP0007Host 2610.10.11, 10.10.14
AP0007Host 3910.10.11, 10.10.16
AP0008Host 810.10.200, 10.10.200
AP0008Host 2110.10.11, 10.10.16
AP0008Host 3410.10.200, 10.10.207
AP0009Host 910.10.200, 10.10.208
AP0009Host 2210.10.200, 10.10.209
AP0009Host 3510.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. 

 

 

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.

6 Replies

  • rsbin's avatar
    rsbin
    Community 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,

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

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

      Anonymous ,

      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,

      • Anonymous's avatar
        Anonymous
        Not applicable
        3 years ago

        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.

         

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi Anonymous,

    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