I have a tricky one here i was hoping someone could lend a hand
I have a 3 tables hierarchy
I have some other tables that are related but not owned by the tables above
When the user creates an order and adds a product they also create a component for their product, the component has an associated expense. The user then chooses whether or not to assign that component to the Inventory
So each order has two types of expenses
I am trying to sum the total Expenses per order, to do this i have created some slices
Cash Expenses (slice of expenses table)
Inventory Expenses (slice of inventory out table)
However there are circumstances where if i simply sum to the two slices together some entries may be double counted due to the way the inventory cost calculations must occur.
So my question is what should my filter formula look like and where does my filter formula go to filter out Inventory Charges where there is a duplicate Component Key already found within that Order in the Expenses table? (I am wanting to show and sum the Inventory Expense only if there is no Corresponding Cash Expense already found)
I have tried using the below filter expression to compare the two lists in a few places, but not having any success, should i be working on getting this into a slice condition for the Inventory Expenses, or creating a filtered REF_ROWS VC column in the orders table?
(ISNOTBLANK(FILTER(“Inventory Out”, [Component Id] = [Component Id]) - LIST([Component Id]))))
Any ideas or guidance?