One example: the humble chest X-ray. You may think the main risk is radiation, but IMO you'd be wrong to think that. The main risk is finding something. We call them incidentalomas. You would never have found it if you didn't do the CXR. It would never have caused you worry or morbidity. But now we know it's there, you need to get a lung biopsy. And that leads to a pneumothorax. That leads to a longer stay in hospital. That leads to time off work. You end up with no benefit to a patient who now has less trust in the medical system because they got a pneumothorax for nothing.
A brain aneurysm poses a certain level of risk based on its size and location. So let's say you have an asymptomatic aneurysm with an annual mortality risk of 0.5%.
Now let's say you decide to randomly have a full body MRI and the aneurysm is discovered. A neurosurgeon tells you that you can have an operation to clip or coil the aneurysm with a 10% risk of fatality due to its location. Do you have the op?
Apparently most people do, despite the overall odds of immediate death. A 0.5% annual risk of death would ride most people out to their age of natural death rather than the 10% risk of the op, but people hate having even a tiny risk hanging over their head long term.
As such, there's a school of thought that you shouldn't have such types of screening (or at least to not know the results if they are minor) unless you are symptomatic.
It hurts to quantify it this way, but the pot to fund health care is not bottomless in either a pure public or privately insured system. It needs to be spread across the entire population, and across all of the potential issues an individual may face.