Since CivitAI is very smart and well-designed, enabling this setting now HIDES your Checkpoint/LoRA from all users that have NSFW visible (i.e., "show all"), which is as intuitive as it sounds. It is not a good idea to ever enable it, unless you absolutely have to or you really, really hate people even knowing that your model exists – for no tangible upside. Maybe moral superiority or something, I don't know.
Fun fact: I had it enabled on one of my LoRAs as a joke and it took me this long to figure out what was wrong with it.
Addendum
A random article like this one isn't exactly the most visible thing and I'm not going to keep bumping it like a jerk. If you spot a model in the wild to which this applies (I semi-frequently see this with Dailly Challenge World Morphs, which really have no reason to set this apart from it doing no harm until now), and you care about the model and/or creator and want to do them a solid - consider leaving a comment suggesting that they should think about changing it. It really is that insidious an issue because it's counterintuitive and not at all obvious. If anyone is worried this may flood their model gallery with porn, be aware that there are entirely separate moderation tools for that, and as far as I'm aware they're just as effective, if not more so.
But wait, it gets worse!
What the actual fuck? Turns out you're not just sabotaging yourself, but also everyone using your model, even outside the online generator. I'm going to start giving negative reviews to every model using this that doesn't have a very clear reason to have it on.