Tricky formula - counting first child of related parent

I have tables called Clients, Food Orders and Order Manifests.  The Clients table and the Order Manifests table both  have  Related Food Orders columns.  Within each Order Manifest, I want to capture a count of the number of clients whose first-ever food order is included within this Order Manifest.  

Thoughts?  Much appreciated.

 

 

0 2 61
2 REPLIES 2

I've figured out one approach, but would like to know if there's a better one. 

Here's what I did:  In clients, I added a vc called First Order Manifest which stores a ref:

Lookup(MIN(
SELECT(
Order Manifests[_RowNumber],
IN(
[_THISROW].[Row ID],
[Related Food Orders][Client]
)
)
),
Order Manifests, _RowNumber, Row ID)

And added a vc in Manifests that looks for it's own ref in clients and counts them:

COUNT(
SELECT(
Clients[Row ID],
[First Order Manifest] = [_THISROW].[Row ID],
)
)

Is there a more efficient way? 

Maybe this

COUNT( SELECT(
  [related foodOrders][row id] ,
  [row id] = MINROW( foodOrders , timestamp/rownumber , [client]=[_THISROW].[client] )
) )
Top Labels in this Space