topic Re: Find values that are on the many side of a relationship but not on the "one" side in DAX Commands and Tips

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

Jodownunder_ — Fri, 15 Nov 2024 02:13:42 GMT

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,

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

isjoycewang — Fri, 15 Nov 2024 02:33:08 GMT

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

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

Jodownunder_ — Fri, 15 Nov 2024 02:44:38 GMT

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!

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

FreemanZ — Fri, 15 Nov 2024 05:23:32 GMT

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.

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

divyed — Fri, 15 Nov 2024 05:34:24 GMT

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

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

Anonymous — Fri, 29 Nov 2024 03:09:48 GMT

Hi Jodownunder_ ,

I create two tables as you mentioned.

Then I create a new table.

Table = EXCEPT( DISTINCT('Table2'[OrderID]), DISTINCT('Table1'[OrderID]) )

Best Regards

Yilong Zhou

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