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.
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.
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,
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 ,
[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.