It's not the fault of MSI per se, although its concept seems utterly frightful to me, but the way DLLs work and are registered and where you have to install them etc. When your app requires an older or newer MSVCRT etc. Also...the registry...something else that makes good old UNIX text file editing seem good to me.
I always thought that libsomelibrary.so.MAJORVERSION.MINORVERSION with symbolic links from libsomelibrary.so.MAJORVERSION and libsomelibrary.so allowed one to create dependencies that made sense - more or less specific as you chose. It also allows different versions of the same library to exist in the same directory such that one is less likely to break existing software when installing something newer (or older).
To be fair it might just be the way Windows tries to keep compatibility for the ages that makes it super difficult. I do think that you tend to get similar outcomes from similar assumptions/requirements and compatibility was indeed very very important till the internet became good enough to offer everyone easy upgrades.
I'm not a big snap or flatpack fan but DMG seems rather in their class - making things simpler. It might be easy to run an MSI after someone has burned many hours testing it to get it to work everywhere but to me it's a non-simple architecture that is made to work by extreme effort. I'd rather a simpler architecture that open source packagers could manage more easily.
As an example, I tried creating an Ubuntu package recently - a plugin for GNU Make that adds some useful functions.....that was SO hard - worse than any MSI experience I ever had - and I gave up. There were millions of confusing instructions and none worked properly. I couldn't work out which way was up or down - I don't know how people do it.
On Artix (an OS which is a little harder to install than Ubuntu) it is an absolute dream to create packages - exceedingly so. It might be that they aren't the greatest packages in the world but something that can be developed easily and got working can also be improved easily.