Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
vnqt
Helper V
Helper V

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. 

 

 

1 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.

View solution in original post

6 REPLIES 6
v-shex-msft
Community Support
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
vnqt
Helper V
Helper V

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. 

@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,

@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.

 

@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
Super User
Super User

@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,

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.