Checking Table Values not in another or in another Table Column

[fe_username]

Dear Guru of PowerBI,

I've a table A called Server with a column
host_name and 
a table Backup with
client_name (name of Server that has a backup).

Server_presentNotPresent_tbl = 
VAR filtered_server_not_present = 
    ADDCOLUMNS(
        FILTER('server',
            'server'[inactive] = FALSE() &&
            'server'[backup] = "backup"&&
            NOT 'server'[host_name_value] IN VALUES('commvault'[client_name])),
        "Status", "Not Present"
    )

VAR filtered_server_present = 
    ADDCOLUMNS(
        FILTER('server',
            'server'[inactive] = FALSE() &&
            'server'[backup] = "backup"&&
            'server'[host_name_value] IN VALUES('Backup'[client_name])),
        "Status", "Present"
    )

VAR risultato = UNION(filtered_server_present, filtered_server_not_present)

RETURN
    risultato

The table returned is wrong.
I took both tables in excel and I did a vlookup from Hostname of Server to commvault table column client_name.
The number doesn't match.

tl:dr : I have a table Server and a table Backup, I need to find the list of Server host_name that are present or not present in table Backup

[123abc]

Here's a revised version of your DAX formula to achieve your goal:

Server_presentNotPresent_tbl =
VAR AllServerHostNames = VALUES('server'[host_name])
VAR PresentServerHostNames = VALUES('Backup'[client_name])
VAR NotPresentServerHostNames = EXCEPT(AllServerHostNames, PresentServerHostNames)

RETURN
UNION(
ADDCOLUMNS(INTERSECT(AllServerHostNames, PresentServerHostNames), "Host Name", [client_name], "Status", "Present"),
ADDCOLUMNS(NotPresentServerHostNames, "Host Name", [host_name], "Status", "Not Present")
)

This revised DAX formula does the following:

1. It creates a list of all server host names in the "Server" table and stores it in the variable AllServerHostNames.
2. It creates a list of server host names present in the "Backup" table and stores it in the variable PresentServerHostNames.
3. It calculates the server host names that are not present in the "Backup" table by taking the set difference between AllServerHostNames and PresentServerHostNames.
4. It uses the UNION function to combine two sets of host names: one for "Present" and another for "Not Present."
5. The ADDCOLUMNS function is used to create a new column "Status" based on whether the host name is "Present" or "Not Present."

This DAX formula should provide you with a table that correctly identifies the server host names that are present or not present in the "Backup" table.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.

[fe_username]

I have a problem that I need to filter AllServerHostNames by inactive = False and Backup = "Backup". How can I implement the filter ?

[123abc]

To filter the AllServerHostNames by the conditions inactive = False and `Backup = "Backup," you can use the FILTER function to create a new filtered table. Here's how you can modify the DAX formula to include these filters:

Server_presentNotPresent_tbl =
VAR AllServerHostNames = FILTER('Server',
'Server'[inactive] = FALSE() &&
'Server'[backup] = "backup"
)

VAR PresentServers = FILTER(AllServerHostNames, COUNTROWS(FILTER('Backup', 'Backup'[client_name] = AllServerHostNames[host_name_value])) > 0)
VAR NotPresentServers = FILTER(AllServerHostNames, COUNTROWS(FILTER('Backup', 'Backup'[client_name] = AllServerHostNames[host_name_value])) = 0)

VAR PresentResult = ADDCOLUMNS(PresentServers, "Status", "Present")
VAR NotPresentResult = ADDCOLUMNS(NotPresentServers, "Status", "Not Present")

VAR ResultTable = UNION(PresentResult, NotPresentResult)

RETURN
ResultTable

In the modified formula, AllServerHostNames is a filtered table containing only those records from the 'Server' table where inactive is False and backup is "backup." This is the table you will use as the basis for further analysis.

The rest of the formula then proceeds to find the servers that are "Present" or "Not Present" in the Backup table based on the client_name values and adds the "Status" column to each of these filtered tables. The final result is a union of the two tables with the server status.

This should give you the desired results for servers meeting your specific criteria.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.