I have 4 tables
Running Sheet 2
I have a slice to only show customers with open orders in the app. I also want to only show customers who have an order with a delivery date that matches the delivery date in another table that is chosen by the user. (Running Sheet 2)
Below is the incorrect expression and google sheet with the chosen date of 19/12/2019. How do I get the expression to compare delivery date on the outlets order with the delivery date in the “running sheet 2” table referenced by unique id “1”?
AND(COUNT(SELECT(Orders[Order Id],AND([_THISROW].[Outlet No]=[Outlet No],[Order Status]=“Open”)))>0,
COUNT(SELECT(Orders[Order Id],AND([_THISROW].[Outlet No]=[Outlet No],[Delivery Date]=Running Sheet 2[Delivery Date], [Unique Id] =“1”)))>0)
“Running Sheet 2” table
|Unique ID||Delivery Date||Excluder||Route||Order Status|
Alternatively I have an expression that looks up the contents of the running sheet table values and matches rows to" order status" open and “delivery date” 19/12/2019 but it works when the slice is of the Orders table.
How can i adjust it so that it is a slice of the customers table and returns customers with orders where the delivery data and order status match what the user has selected.
AND(CONTAINS(Running Sheet[Delivery Date],[Delivery Date]),CONTAINS(Running Sheet 2[Order Status],[Order Status]))
The reason for doing this is so that a report can be produced from the slice too referencing child and grandchild tables.