Forum Discussion
Comparing Two Different Datasets for missing values
Hi Anonymous
You can create a new calculated tableMissing From b = EXCEPT ( VALUES ( Table_a[Column A] ), VALUES ( Table_b[Column A] ) )Or you can create a measure, for example for a card visual
Missing From b = CONCATENATEX ( EXCEPT ( VALUES ( Table_a[Column A] ), VALUES ( Table_b[Column A] ) ), [Column A], UNICHAR ( 10 ) )
Anonymous
I think using the LOOKUPVALUE function should do the trick.
Create a new Calculated Column in your table_a. The syntax should be something like this:
ColumnA_fromTable_b = LOOKUPVALUE( table_b[Column A],table_b[Column A], [Column A] )
From Microsoft documentation:
LOOKUPVALUE(
<result_columnName>,
<search_columnName>,
<search_value>
[, <search2_columnName>, <search2_value>]…
[, <alternateResult>]
)
Hope this works for you.
Regards,
I think the naming convention I used for the columns and tables is a bit confusing so let me use the actual names.
Within the table named CMDB_Active and the column within that table called ip_address, I am trying to see what values are contained in that column that aren't in a separate table called it_current from the column ip_address_rapid7
Let me know if that makes sense
Anonymous ,
It would be helpful if you can you paste a small sample of data (just several records) from each table into a reply. Paste it as a table, not a picture.
Anonymous ,
I created two sample tables:
ip_address_rapid7
b c d This is the result
So the values "a", "e" and "f" are not in your second table called it_current.
Hope this helps.
Another error, I used the following to create a new column in Table A:
Missing from Rapid7 = LOOKUPVALUE(it_current[ip_address_rapid7],it_current[ip_address_rapid7], [ip_address])
Error: The column 'CMDB_Active'[Missing from Rapid7] cannot be pushed to the remote data source and cannot be used in this scenario.