Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet 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
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.
Solved! Go to 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.
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
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,
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.
@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,
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
92 | |
86 | |
84 | |
66 | |
49 |
User | Count |
---|---|
140 | |
114 | |
108 | |
64 | |
60 |