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