Display if not found

[Anonymous]

I have two tables.

Table K027 Status is returning 17 distinct "Work Order" numbers.

Table "TJ" has two columns, "Work Order Number" and "Location".  There will be multiple rows with the same work order number.

Of the 17 distinct work orders in Table K027, I want to display the work orders that are not found in Table "TJ" and of the work orders that are found in Table "TJ", do not display them if the word Complete is in the "Location" column.

And, do I have the tables join properly for what I'm trying to accomplish?

Please advise.  I'm sorry, I do not know how to submit sample data.  If someone wants to direct me to a page that will show me how to submit sample data on this site, I will learn.

[Phil_Seamark]

Hi @Anonymous

Just checking your logic here

"Of the 17 distinct work orders in Table K027, I want to display the work orders that are not found in Table "TJ" and of the work orders that are found in Table "TJ", do not display them if the word Complete is in the "Location" column."

You only want to display work orders from K027 if they don't exist in TJ.  That would mean you wouldn't show any work orders that exist in TJ regardless of what the location column carries..

[Anonymous]

K027 has wo# 123, 456, 789

TJ has:

wo# 123 - location "B"

wo# 789 - location "Complete"

I want the following to be returned:

wo# 123  - because is was found in TJ, but "Complete" was not found in "location"

wo# 456  - because is was not found in TJ

wo# 789 would not be returned because it was found in TJ and also had Complete in the "Location"

In essence, I want to return every work order number that is not Complete.  If it finds it in TJ, but not complete, return it.  If it is not found in TJ, it is not complete because it has not even had time to be entered on the report to get started.  Hope this makes sense.  

[Phil_Seamark]

Hi @Anonymous

This calculated table gets close to what you need

Table = 

VAR T1 = VALUES('K027 Status'[Work Order])
VAR T2 = SUMMARIZECOLUMNS('TJ'[Work Order Number],FILTER('TJ',[Location]="Complete"))
RETURN EXCEPT(T1,T2)

[Anonymous]

Thanks for your help on this,  I don't know how to insert a calculated table but I added a measure following your example.  Here is my error.  TJ might have two with the same wo#.  If it does and either one of them does not have location "complete", I need it shown.

Example:

TJ table:

wo# 123  location: 1

wo# 123  location: Complete

I would want wo# 123 to be returned because one line is still not complete.

[Phil_Seamark · Accepted solution]

Hi @Anonymous

To create a calulcated table, click the New Table button on the modeling tab.

This creates a new table in DAX but leaves your original tables intact.  Useful for a bunch of reasons.

[Anonymous]

Perfect.  It worked.  THANK YOU!!