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.
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] )
) )
User | Count |
---|---|
33 | |
30 | |
30 | |
19 | |
17 |