Forum Discussion
Display if not found
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.
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.
6 Replies
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..
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.
Hi Anonymous
This calculated table gets close to what you needTable = VAR T1 = VALUES('K027 Status'[Work Order]) VAR T2 = SUMMARIZECOLUMNS('TJ'[Work Order Number],FILTER('TJ',[Location]="Complete")) RETURN EXCEPT(T1,T2)