Forum Discussion
Count value based on the value in other table
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.
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.
Community ChampionAnonymous ,
[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.