There's very little voltage between N & E under normal conditions. Assume the circuit is running at something near maximum voltage drop of say 10V (4.3% of 230V) therefore there is 5V drop along the line conductor and 5V along the neutral. If we also assume that whatever is causing the IR is located in the worst place so that the 5V appears across that 0.4MΩ, then it's just ohms law and the leakage would be 5/0.4M = 12.5μA. I.e. so tiny that it's completely swamped by normal functional leakage. Even with a resistance 1000 times lower, at just 400 ohms, the worst-case leakage would still only be 12.5mA and less than the tripping threshold, and that's with 5V between N & E. Near the board on a TN-C-S supply, the drop might only be a fraction of a volt, in which case it is going to take a lower resistance still, perhaps just ten ohms or less, before it trips. That is how even solid neutral-earth faults can lie low until there is enough load on the system to drive up the N-E voltage.
Obviously if the low IR is located L-E instead then the leakage is 230/0.4M = 0.6mA. With no other leakage sources, a typical RCD that trips at 22mA would need a resistance below 230 / 0.022 = 10.5kΩ to trip it. This can be disconcerting when reading the IR on digital display that shows nnn.nn MΩ. The display can legitimately show 0.00 MΩ and yet the insulation is still high enough to not trip. Only by going to a higher resolution do you see the difference between say 12kΩ which probably won't trip, and 2kΩ which will.