For me, photo/videography is one of the main reasons to dive. As a general rule, I do not hesitate to take a camera even to pinnacle dives. Apart from course dives, during a pinnacle dive I am more likely to have a camera with me than on a normal everyday dive to well known site. Taking a camera with me might even lower my stress levels and improve gas consumption compared against to diving without one and being more "environment aware" i.e. anxious of a new environment;-)
For previously unvisited wrecks it is almost a must to take a camera with me. The exception would be difficult sea conditions on the surface. But then we are very close the thumbing the dive anyway.
For cave dives, I typically take a camera with me especially to visits to new sites. However for new sites, I prefer to use an easy-to-use video camera and just shoot continuously. It is not that difficult to swim in trim and keep the framing good. And the unedited footage is great for learning the cave afterwards. Shooting video with a DSLR requires two more brain cells and the threshold for taking a DSLR camera with me is a bit higher.
However, there are situations where I might not take a camera with me:
1) occasionally new circuit/traverse dives (in mines) without a setup dive. Here I prefer to streamline myself in order to minimize gas consumption.
2) dives where the dive is a bit demanding to my dive buddy/team and they have not got any brain cells left to carry and handle off-camera video lights.
