/Hi,
I have 4 tables
Customer
Order
Order Details
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 |
| 1 |
19/12/2019 |
Cancelled |
|
Open |
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.
Expression below
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.
Thanks
Phil
LinkedIn
Twitter
