Adding an Additional Column to a newly created table from a separate table that correlates w/ column

[teaguejs]

Hi,

I recently created a new table based off a comparison between two columns for missing values. 

I used the following DAX statement to create the new table (called "Missing From Rapid7"):
Missing From Rapid7 =
EXCEPT( VALUES(CMDB_Active[ip_address]), VALUES(it_current[ip_address_rapid7]))

So based on those values within that table, I am trying to add an additional column in that table that contains the values from one of the original two tables (from the table called "CMDB_Active")

For example, I want to add the column "host_name" that contains the values from table "CMDB_Active" in this newly created table called "Missing From Rapid7".

Each of the values in the "Missing From Rapid7" is from a column called "IP Address". And each of the values in the "IP Address" column has a corresponding "host_name" value that is from the table "CMDB_Active".

Let me know if this makes sense or any further info is needed!

[AntonioM]

You could try this in a couple of ways. One way would be to use ADDCOLUMNS, something like

Missing From Rapid7 =

ADDCOLUMNS(
        EXCEPT( VALUES(CMDB_Active[ip_address]), VALUES(it_current[ip_address_rapid7])),

        "Host Name",

        LOOKUPVALUE(CMDB_Active[host_name], CMDB_Active[ip_address], [ip_address])

)

Where [ip_address] is the name of the column in the newly created table, which should be ip_address if you haven't changed it.

Another option is to do all of this in Power Query. If you 'merge as new' between CMDB_Active and it_current, using a 'left anti join', then you would have the entire CMDB_Active table, only for ip_adress that are not in it_current.

Simple example, 2 and 3 are gone and you still have the 'word' column, which wasn't used in the join.

[teaguejs]

Hi, thanks for the help! I ran into a couple issues trying the first option.

First I used the "New Column" option with the following expression:

Column = ADDCOLUMNS(
EXCEPT( VALUES(CMDB_Active[ip_address]), VALUES(it_current[ip_address_rapid7])),

"Host Name"

LOOKUPVALUE (CMDB_Active[host_name], CMDB_Active[ip_address], [ip_address])

)

I received the error: 
The syntax for 'LOOKUPVALUE' is incorrect. (DAX(ADDCOLUMNS( EXCEPT( VALUES(CMDB_Active[ip_address]), VALUES(it_current[ip_address_rapid7])), "Host Name" LOOKUPVALUE (CMDB_Active[host_name], CMDB_Active[ip_address], [ip_address])))).


So then I added a comma after "Host Name", and got the following error:
The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value.

[AntonioM]

You're right, I missed a comma after the new column name (I've added that in now). 

The idea with LOOKUPVALUE is to 

• use the current value of [ip_adress] from the new table you're creating with EXCEPT
• match that to the value of CMDB_Active[ip_address]
• look at the coresponding CMDB_Active[host_name]

Is it written exactly as below (with the comma this time)? I've tried that on a very quick setup and it seemed to work fine for me. If not, could you share some of the data?

Missing From Rapid7 = 
ADDCOLUMNS(
EXCEPT( VALUES(CMDB_Active[ip_address]), VALUES(it_current[ip_address_rapid7])),
"Host Name",
LOOKUPVALUE(CMDB_Active[host_name], CMDB_Active[ip_address], [ip_address])
)