Two Subset of List - How to do it in DAX

[f_e_user]

Dear Guru of PowerBI,
I've my data model

What I'm trying to achieve is to find the Server that are not related to a Business Service.
Server links to Business Service through Server.sys_id = Business_Service.child
I managed to calculate the formula and verify it from client :

Total Server =
CALCULATE(DISTINCTCOUNT(SYS_ID['Server']),
FILTER('Server', Active = "True"))

Total server_BS= CALCULATE(DISTINCTCOUNT(child['Business Service']),
FILTER(Server, Active = "True"))

The Server that are not linked to a BS is given from measure Total Server (3560) - Total Server_BS(3500).
So in this case, there are 60 Servers that have not been linked to a Business Service.
I need to create a Drill Through Page where I show the these 60 Servers.
I don't know how to do it in DAX, if it would be Python it would be like this :

List_sysid_server = pd.Dataframe(df.id.filter(df['Active'] = True))
List_sysid_BS     = pd.Dataframe(dfBS.id,filter(df['Active']= True))

#I Have Two Arrays of sys_ID (Sys_id is like "23ad9402adh24bt")
#First Array contains sys_ID of Server
#Second Array contains Child (that is Foreign Key, is a SYS_ID)
#I need to obtain in dax, the SYS_ID in first Array that are not in second Array
List_sysid_not_related = set(List_sysid_server - List_sysid_BS  )

How can I show that 60 IDs that are Present in the server but not in the child column with DAX Logic?

[Anonymous · Accepted solution]

Hi @f_e_user ,

According to your description, here are my steps you can follow as a solution.

(1) This is my test data. 

(2) You can use the EXCEPT() function to create a table.

Servers Not Linked to BS = 
VAR List_sysid_server = FILTER('Server','Server'[Active]="TRUE")
VAR List_sysid_BS = FILTER('Business_Service','Business_Service'[Active]="TRUE")
RETURN  EXCEPT(List_sysid_BS,List_sysid_server)

(3) Then the result is as follows.

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.

Best Regards,

Neeko Tang

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