Measure for Primary Key ID not found in Foreign Key table

[Anonymous]

Hello,

I'm trying to filter a measure to only show the IDs that are not found in a related table. 

What I have currently:

Closed Won Opportunites without Request =

     CALCULATE([Count of Closed Won Opportunities], NOT(Opportunity[OPPORTUNTIY_ID] IN 'Sales Engineering Request'))

Any help is very appreciated!

[v-jianboli-msft · Accepted solution]

Hi @Anonymous ,

Based on your description, I have created a simple sample:

Please try:

Measure = IF(NOT( MAX('Main Table'[Primary Key ID]) in SELECTCOLUMNS(ALL('Related Table'),"ID",[ID])),1)

Apply it to the visual:

If you need a table:

Table = EXCEPT(VALUES('Main Table'[Primary Key ID]),VALUES('Related Table'[ID]))

Output:

Best Regards,

Jianbo Li

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

[djurecicK2]

Hi @Anonymous ,

 What is the result of the measure you've posted? You might need to change this section since I'm guessing that the ID field does not contain the text 'Sales Engineering Request'

NOT(Opportunity[OPPORTUNTIY_ID] IN 'Sales Engineering Request'

If the field you are looking for is a related table, you can get the value using RELATED:

https://learn.microsoft.com/en-us/dax/related-function-dax

https://dax.guide/related/

Please accept as solution if this answers the question- thanks!