Most of the time we're trying to get a 'good picture' of typical scenes so we'd only want filters that augment that purpose. ND, Polarizing, UV and combinations of these (they screw together) provide a means of improving
the image of the existing scene while not distorting
Most other filters provide 'creative distortion' of reality or are special purpose. I've used very few distorting filters (borrowed, never bought) to get effects with light rays and such but I always found things like this too artificial for my taste. Might be OK for magazine covers, but not for typical documentary
photography which depends on real-word accuracy.
There used to be filters for use with special purpose films like Infrared and Ultraviolet sensitive rolls. You had to shoot the whole roll (generally 12 or 24 frames) dedicated to your purpose in selecting that variety.
I never tried the ultraviolet-sensitive stuff but did use IR films a few times for night photography. For flash use I had a filter that went into the flash-hood. The filter on the flash blocked all visible light (with the help of some black electrical tape) and only the IR got through, avoiding the tell-tale 'flash'. It was fun to put that IR filter on the flash unit and go around taking pictures of friends at late night outdoor events when they thought I wasn't actually taking pictures...
Not sure modern DSLRs do this but I did notice the little red IR-focus dot on a number of modern lenses, so maybe.
I probably got the most use out of the Polarized filter as it works well in fog, rain and the glare of bright sunlight. It works well for shots that include the skyline, mountains and any water shots. I liked it best for shots taken on brightly lit water or snow as it reduces the intense 'glowing glare' that often wrecks shots.