Find values that are on the many side of a relationship but not on the "one" side

[Jodownunder_]

Hi,
I feel like this is an easy question but I've been going around in circles so thought I'd try the brains trust.
I have 2 tables with a one to many relationship.
In table 1 its one order per line but in table 2 there could be several lines with the same order number depending on how many different products are on the order. 
Table 1 (one side) Orders from one system
Table 2 (many side) All orders in a production system

Employees can also enter orders manually directly into production system (table 2) so I'm trying to find the DAX to see if there are any current orders in table 2 that are not in table 1. I can look in table 1 for orders not in table 2 but not the other way around. 
Any help apreciated.
Thanks,

Jo

[divyed]

Hello @Jodownunder_ ,

You can try below dax to add a calculated column in table 2 .

NotInTable1 =
IF(
ISBLANK(
LOOKUPVALUE(
Table1[OrderID],
Table1[OrderID],
Table2[OrderID]
)
),
TRUE,
FALSE
)

Alternatively you can create a measure as well :

OrdersNotInTable1 =
COUNTROWS(
FILTER(
Table2,
ISBLANK(
LOOKUPVALUE(
Table1[OrderID],
Table1[OrderID],
Table2[OrderID]
)
)
)
)

I hope this helps .

Did I answer your query ? Mark this as solution so that other members can find this quickly. Kudos are appreciated.

Cheers

[FreemanZ]

hi @Jodownunder_ 

try like:

1. plot a table visual with table2[order] column, do not aggregate.

2. pull table2[order] to the filter pane of the table visual and select blank.

then you shall see all the orders kissing in table1.

[Jodownunder_]

Thanks but that doesn't work - it just shows all the ones in table 2 saying "Yes" that have a match but doens't show any with "no". And I know there are some!

[isjoycewang]

Hi @Jodownunder_,

I'd try to make a demo based on your description. Please try below column:

In_Table1 = 
IF( Table2[OrderID] IN VALUES(Table1[OrderID]), "Yes", "No")

Please provide a dummy file sample if the demo situation doesn't work for you. Thanks.

Best Regards,

Joyce