The most important difference between passive and active DIs is that the latter have a much higher input impedance: usually something like 200 kΩ, whereas a passive DI "inherits" the low impedance of the mic input you're feeding (around 2kΩ), or transforms it up a bit to something in the 20 kΩ range. That makes hardly a difference for instruments with low output impedance (e.g. keyboards, acoustic guitars with build-in preamp): if the output impedance, e.g. 300 Ω, is much lower then the input impedance, you're in "voltage saturation mode", then it makes no difference whether the input impedance is ten times as high or a thousand times as high. So for "powered" instruments you'll always be pretty ok with a passive DI.
The story is very different for instruments that are themselves passive. In particular, guitar pickups have a resistance up to some 10 kΩ even in DC, and the inductive impedance is much higher. So if you plug an electric guitar into a passive DI, the transmission becomes current-saturated. In this mode, the pickups operate completely different from voltage-saturation, the resonance almost vanishes and the volume pot becomes very quiet much quicker. So if you're DI-ing a guitar and don't want to alter the sound even before processing, you must use an active DI, whose input impedance is close to that of an electric-guitar amp (those range between 50 kΩ and 500 kΩ).
It's even more drastic for acoustic guitars with passive piëzo pickups: those have a high capacitive impedance. If you connect them to a low ohmic impedance, you're building a high-pass filter, cutting away all bass frequencies (often in fact everything below 1 kHz). That's why acoustic instruments, unlike electric guitars, almost always have dedicated preamps with input impedance > 1 MΩ: because even standard active DIs have too low impedance to get an even frequency response.
I should add a bit more detail on impedance matching in a passive DI. As I said, passive DIs can also increase the input impedance: by transforming down, i.e. using a transformer with more windings on the primary side than the secondary. The cost is that the output voltage is reduced. Not all DIs do this, for some models I've used I'm sure the output was actually in the same range as the input. (Strangely, it isn't usually specified on a passive DI what winding ratio is used.) But Wikipedia quotes ratios between 10:1 and 20:1 as typical, which is definitely more than I would have reckoned. With such high ratios, voltage loss becomes an issue, but good mic preamps should have enough SNR so this should still be ok; after all, dynamic microphones also offer rather poor voltage. At any rate however, even if you transform a 2 kΩ input up to 40 kΩ, this is still rather on the low end for electric guitar, so my point stands: the main issue with passive DIs is low input impedance.
Now, it would certainly be possible to design a passive DI with transformer ratio as high as perhaps 100:1. Then the input impedance would be high enough to connect guitars, perhaps even a piëzo – but the output voltage really wouldn't be satisfying anymore, no matter what instrument you connect. So I think the current situation is pretty fine for practical purposes:
- Active DIs can easily offer enough input impedance for most instruments, without sacrificing gain (though they normally offer a switch to do that on purpose, if your inputs are overloaded). If you need high impedance, use an active DI.
- Passive DIs don't aim at very high input impedance, so as not to sacrifice too much gain either – and that's ok, because for active instruments impedance is not really an issue. Hence, for those instruments a passive DI is a cheap, simple, reliable alternative. Also, you may use them to deliberately dampen a guitar's pickup resonance; perhaps that's worthwhile sometimes. (A few guitar effects pedals have low input impedance, best known the Fuzz Face, and the thus changed guitar response makes up a good part of many fuzz sounds you can hear on old records.)