I have two related tables:
SD with columns: No, Date, etc.
Orders with columns: SDNo_, Date, etc.
Both are related with this No - SDNo as one SD to many Orders.
There some No, which are present in Orders and not present in SD.
So I need to make a slicer or a button, or anything, that would show a list of Orders where No<> 0 and there is no respective No in SD.
What is the best approach to achieve it?
I have tried to create a calculated table in many ways:
FILTER(
'Orders',
'Orders'[SDNo_] <> 0
&& ISBLANK(RELATED('SD'[No])))
or
FILTER(
'Orders',
'Orders'[SDNo_] <> 0
&& ISBLANK(
LOOKUPVALUE(
'SD'[No],
'SD'[No], 'Order'[SDNo_]
)
)
)
or
FILTER(
'Orders',
'Orders'[SDNo_] <> 0
&& NOT(
CONTAINS(
'SD',
'SD'[No], 'Orders'[SDNo_]
)
)
)
Nothing worked. The calculated table still contains Orders with SDNo, which are not present in SD-table.
Is it possible to achieve what I need without merging the table in Power query?