The problem is binary artifacts of compiled software. Those native binaries have hardcoded paths to dependencies and so by changing the location, you're forcing homebrew to compile everything locally to use a different path. It's the same reason Nix is still at /nix, despite the quirky installation requirements it forces on modern Macs with SIP.
There are tools to rewrite links in binaries, but they only work until you get a program calling dlopen or equivalent
It does work, it's just not supported because the experience is worse. Open source (Homebrew included) is full of lots of plans: it's lacking people willing and able to execute on those plans. When or if that happens: Homebrew will support installations in more locations.
Because of the hardcoded paths, the precompiled artifacts will not work. Because of this, homebrew will need to run the build script locally. This means that the process takes longer, which is what makes the experience worse.
There are tools to rewrite links in binaries, but they only work until you get a program calling dlopen or equivalent