DPI does matter. It doesn't have as much of an impact at lower resolutions but with high resolutions it becomes more important.
DPI at it's basic level is how precise it's tracking is over an inch of mouse movement. DPI performance at a given resolution is important, because if you if you take a 400X400 pixel screen, a 400 dpi mouse will cross that screen with one inch of movement with NO software acceleration at any given time.
Now, lets consider a 1920X1920 pixel wide screen. That same mouse would require almost 5 inches of mouse movement to cross the screen, which would put you well off the mouse pad. The answer to this is software acceleration, which basically tells Windows that for every 1 DPI the mouse registers, move X more. By doing this, they can make a 400 dpi mouse cover 1920 pixels in say 2 inches by moving the curser 2.4 pixels for every 1 DPI. This works, but you lose sensitivity if your making small movements. You can imagine that it would be a pain to aim a gun in game if everytime you moved the mouse even a little the crosshairs would jump farther than you wanted. This is what happens with a low dpi mouse on a high resolution/high mouse accelerated setup.
Now lets say your mouse has 2000 DPI. Suddenly you can cross a a 1920X1200 screen with about 1 inch movement. "But the curser moves TOO fast when I use that setting!" someone screams. True, but you need to turn down software mouse acceleration to compensate, but this is where the good stuff happens. Remember, the less acceleration you apply to the mouse in software, the more precision you gain at the mouse itself. Suddenly your mouse can track the smallest movements of the hand even at much higher resolutions.
There is a threshold where DPI is too high for a given resolution, but that is very easy enough to figure out. 4000 DPI would be silly for a 1080P display, however once the resolution starts climbing again it becomes more important.
That being said, really high DPI can still be used to give advantages in games where it is nice to have ridiculous tracking speeds, such as a tank turret that takes way to long to turn, but these types of advantages are few and far between.
Bottom line, it isn't a gimmic. It's not a magical number where more is automatically better. It's a tool that will help you optimize your controls. If you buy a fancy mouse and just max the DPI expecting better gameplay you will be dissappointed. However, if you take the time to find your optimal DPI given your particular setup, you would be amazed at the level on control you can have.