Using - append=t prestats=t and getting the "BY" to work correclty

[robertlynch2020]

@Kenshiro70  I have just read your most brilliant answer hear

https://community.splunk.com/t5/Splunk-Search/What-exactly-are-the-rules-requirements-for-using-quot...

I have applied it to a one use case, but I am a little stuck now on another use case and I was hoping you might be able to give me 5 minutes, please. 

The following code is working. I have used it to replace a join. The issue is when I need to add a third mstats.

There are just some rules i can't see to understand or crack etc... Any help would be  great - cheers. It is when I am adding additional ""by" clause "used.by".

I supost the really question is how to handle this when there are multiple BY form different | mstats

| mstats append=t prestats=t min("mx.service.status") min(mx.service.dependencies.status) min(mx.service.resources.status) min("mx.service.deployment.status") max("mx.service.replicas") WHERE "index"="metrics_test" service.type IN (agent-based launcher-based) AND mx.env=http://mx20267vm:15000 span=10s BY "service.name" "service.type" 
| mstats append=t prestats=t max("mx.service.replicas") WHERE "index"="metrics_test" AND mx.env=http://mx20267vm:15000 service.type IN (agent-based launcher-based) span=10s BY service.name 
| eval forked="" 
| mstats append=t prestats=t min("mx.service.deployment.status") max("mx.service.replicas") WHERE "index"="metrics_test" service.type IN (agent-based launcher-based) AND mx.env=http://mx20267vm:15000 span=10s BY "service.name" "service.type" forked 
| mstats append=t prestats=t min(mx.service.dependencies.status) WHERE "index"="metrics_test" service.type IN (agent-based launcher-based) AND mx.env=http://mx20267vm:15000 span=10s 
| rename service.name as Service_Name,service.type as Service_Type | stats max("mx.service.replicas") as replicas min("mx.service.deployment.status") as Deployment min("mx.service.status") as Status_numeric min(mx.service.dependencies.status) as Dependencies min(mx.service.resources.status) as Resources by _time Service_Name Service_Type forked 
| sort 0 _time Service_Name

Working

This is the code that is not working. I added in a "used.by" in the first tstats as it is needed for min(mx.service.dependencies.status) -  However as soon as i add this i loose a lot of data

| mstats append=t prestats=t min(mx.service.dependencies.status) min("mx.service.deployment.status") max("mx.service.replicas") WHERE "index"="metrics_test" service.type IN (agent-based launcher-based) AND mx.env=http://mx20267vm:15000 span=10s BY "service.name" "service.type" "used.by" 
| eval forked="" 
| mstats append=t prestats=t min("mx.service.deployment.status") max("mx.service.replicas") WHERE "index"="metrics_test" service.type IN (agent-based launcher-based) AND mx.env=http://mx20267vm:15000 span=10s BY "service.name" "service.type" "forked" 
| mstats append=t prestats=t max("mx.service.replicas") WHERE "index"="metrics_test" AND mx.env=http://mx20267vm:15000 service.type IN (agent-based launcher-based) span=10s BY service.name 
| rename service.name as Service_Name,service.type as Service_Type 
| stats min("mx.service.deployment.status") as Deployment min(mx.service.dependencies.status) as Dependencies_x max("mx.service.replicas") as replicas by _time Service_Name Service_Type forked "used.by" | sort 0 - Service_Name _time

NOt working