It does seem like you're running into some of the limitations of the built-in visualizations that we offer. I've been working on both clarifying the existing limitations and making Lens into a tool that will eventually let you work around all of these limitations. For now, here is what I can tell you:
- We do offer a tool that doesn't have any of these limits, and it's Vega. The Vega integration lets you build any Elasticsearch query and then apply post-processing in Javascript.
- In the long term, we will want to support post-aggregation filtering of numbers in Lens. This will work for any chart type. It's a common request for reasons just like the case you're seeing. I'm focused on post-aggregation filtering because none of our built-in visualizaiton
- Yes, the "top hit" aggregation is the same as "Last value". We have named it "Last value" because that's what users want.
- For now, you can use the conditional coloring workaround that you've already identified