Ah but that’s because you’re not GW!
From GW’s perspective what was wrong with Fantasy was that it no longer had the potential to make $$$ dolla bills y’all.
Fantasy had three big and one sort of “upcoming” issue for them. First off, the rules were really convoluted and not at all beginner or new player friendly, unlike 40K, and attempts to make them more like that just riled up the fans in a serious way. Second-off, a lot of people had invested massive amounts in Fantasy over the years and weren’t really buying new models - it was also extremely hard to add new units to the already-gigantic army books Fantasy had. Thirdly, new players needed to spend a huge amount of money just to “get playing” in Fantasy because of the large unit sizes and ineffectiveness of smaller units (this was probably true). The upcoming issue was that GW felt like Fantasy’s aesthetic was passe - now, they’ve actually been proven completely wrong on this, but more on that later - they though the large number of more wildly and/or modern-ly designed fantasy wargames was going to start stealing their playerbase. There were other concerns as well, like that the lore was too convoluted and deterred new players (also proven wrong), and so on, but they were lesser concerns.
So they created AoS, which has massively more accessible rules (in a really extreme way - they didn’t even have point-based army building for like a year or two after it released - just pick your units and if you have a lot more models than the enemy he gets an easier win condition), started with much, much smaller army sizes (like a couple of dozen models was fine), allowed them to go for a much wilder aesthetic and add new armies whilst deleting old ones, forcing people to spend money, and also had vastly simplified lore and no real maps or the like.
AoS has done pretty well, though I’m skeptical it’s done better than just rebooting Fantasy with simpler rules and smaller unit sizes would have.
Once AoS was going, GW decided that Fantasy was a less important IP and could be freely licensed out for AAA games (something they’d been carefully avoiding since the early 2000s). This was where they got proved wrong.
The AAA games, principally Total War Warhammer (1/2/3) and Vermintide (1/2) was massively successful, massively popular, and completely re-invigorated the Fantasy fanbase, whilst adding tons of new fans. People loved the convoluted lore, loved the dark fantasy vibe, love the down-to-earth but wild fantasy that contrasted strongly with how things like Warcraft had evolved. They loved it so much that GW started getting ideas.
And now Fantasy is coming back, not to replace AoS, but to exist alongside it, as a sort of “secondary” game, but with tons of new armies and units, because it’s set like a hundred years before the main Fantasy setting was (so most of the stuff is the same, but they can add more). They’re working pretty closely with the Total War Warhammer guys, Creative Assembly on this - two new armies in Total War Warhammer 3 are being developed by both together - Kislev and Cathay (finally Cathay - China).
Warhammer Fantasy RPGs have been successful for the whole time. There’s also an Age of SIgmar RPG, I own it, but I’m not sure I’d actually want to run it.