If I export an image from Lightroom (as a jpeg) I can specify to resize it to particular dimensions, or keep it as it is. But regardless of that there is also a dimension field as in dots per inch but surely this is just a decision made at printing time I don't understand what effect it has on the exported image.
In general, dpi is the conversion from pixel dimensions (image size) to inch dimensions (on paper... X number of pixels per inch determines inches of paper coverage).
When we resample an image, we can specify its new size dimensions, like say 1800x1200 pixels.
Or, we can specify its new printed size, like 6x4 inches at 300 dpi, which computes the same 1800x1200 pixel dimensions (6x300 = 1800, 4x300 = 1200).
Specifying both dpi and inches of print size normally does this resample computation (computes new size of resampled pixel dimensions - to fit that declared paper size).
But merely specifying only dpi (called scaling, to fit the paper inches) merely stores that number in the image file somewhere (and we might then see some new corresponding print size numbers in inches, but the pixels are unaffected, NOT resampled due to the number). Dpi serves no other purpose, or has no other effect, on digital camera images. Only important at the time of actually printing (and deciding paper size).
The Photoshop resample box allows either method.
I think you are saying Lightroom export does the second method, which merely saves a dpi number for future reference. Any dpi number has no effect on the image or pixels, until possibly the time you may be actually printing it, and decide paper size, when you will surely address it again then.
DPI information is only relevant when the pixels are projected to a device that have defined metric dimensions, so you can measure the size of the projection of one sample (pixel).
Traditionally, computer screens have only a fictitious DPI scale, which is considered something like 72, 96 or 120 etc. dpi, as those are the most probable actual size of the monitors available on the market; and since operating systems usually don't know the metric scale of the actually attached destination screen, this DPI setting is kept as a user setting.
This fictious current DPI setting in OS is used to implement scaling of the font and user controls, or to implement a "display a printable document in actual size" function. But for displaying on a computer's screen, which has it's own intrinsic DPI as a raster device and at the same time can display content in different scale on user's demand, this setting in the image is irrelevant. The best rule in pre-retina era was - use the size in pixels which matched the screen's resolution. Oversampling did not provide any benefit, as a slightly scaled down image looked worse than perfectly fit to screen's resolution, event if it's effective DPI was higher.
But when you are working with a publishing software such as InDesign this setting, embedded in the images would allow you to size the placed image when editing the pages.
The same (in pixels) image with different DPI settings would occur at different scale when placed on a page initially, depending on the tool you use to place the image.
The same applies to word processing software, such as the Microsoft Word.
Actually, it could be used by any software having a concept of physical canvas size as well.
So, if you export with different DPI setting, you can find that it's obeyed by different software systems when initially placing the image on a canvas, or in a sort of "display in actual size" function.
Actually, I found this setting very useful, because it affects the imagery placed in InDesign when you are placing many images of the same size. In my case, tuning this setting alowed to create an image gallery almost automatically, instead of scaling each image individually.
I don’t know why Lightroom gives you the ability to select DPI. As per definition DPI is "the number of individual dots that can be placed in a line within the span of 1 inch" (Wikipedia). So the DPI is directly pending on your resolution and print out size.
Also, if you export an image with e.g. 300 DPI and 1 DPI, you will see that both file sizes are equal.
Maybe, the selected DPI would tell the printer how big your image shall be printed, if you do not tell it any size...