Single Mosfet level shifter does not appear to work

This is quite tricky to follow because the earlier question links to another earlier question, so it's a chain of three discussions to try to understand the purpose. It maybe needs a diagram of your proposed solution in context of your entire system.

Anyway, regarding the level conversion, I didn't understand the circuit, however I was more concerned that you mentioned that the Orange Pi crashed. That's worrying, and makes me wonder if some of the electrical signals or levels are not as expected, or if power is reaching devices through (say) GPIO when there is no power applied to the device, causing unexpected behaviour.

In theory, you could just use some resistors to reduce the voltage levels (i.e. potential divider), e.g. use three resistors chained into a potential divider if you need separate outputs for 3.3V and 5V logic. But I'm not sure I'd do that if it were me. I think (since I don't understand the 24V source signal origin, and if it's really 24V, or has the capability to vary - by the way, make sure to configure the PIC input as a Schmitt trigger input) I'd rather use some additional circuitry, and it may as well be an optocoupler, just to completely isolate things. They are cheap.

Secondly, I feel uncomfortable with the idea of connecting that signal to both the Pi and the PIC chip, because what's the device that's supposed to be in control and co-ordinate things? I think that device is supposed to be the PIC chip, acting as a sort of power management device, therefore, why not give it control of passing signals to the Pi as required. In other words, only translate the 24V signal to be used by the PIC, and the PIC can use GPIO to send a signal to the Pi as required. That way, the PIC is always in control, and won't pass on any input signal glitches to the Pi. It just seems more structured this way (but I don't know the detail).

Thirdly, can the PIC run from 3.3V instead? That way, there's no need to worry about level translations between PIC and Pi at least (but if you do need to interwork 5V and 3.3V logic there in the PIC to Pi direction, then you can just use two resistors as a potential divider at that interface between PIC and PI).

It would be interesting to flip the MOSFET the other way around in your simulation so that the source is connected to the 5V side and the drain to the 3.3V.  In this case when PLOSS5V is high, the MOSFET will be off and the signal on the 3.3V side should be high.  If, on the other hand, PLOSS5V goes low, the MOSFET should turn on... and I suspect the 3.3V signal should be closer to 0V than 1.3V.

I have to admit, that with my earlier explanation I didn't take the internal substrate diode into account.  It would be more valuable on the simulation side of things to keep the original  mosfet orientation as outlined in the app note... and try and find a spice model that includes the internal diode... and then see if there is any impact on using higher versus lower value pull up resistors... in general in the real world when the 3.3V side was only 1.3V it seems like the MOSFET isn't fully on.  One question, when you are testing this in the real world are you keeping 24V on at steady state while taking DC voltage measurements?  If 24V is being pulsed any DC measurements would be scaled down on most multimeters.  For instance a 40% duty cycle on 24V would give you 1.3V.

I think he is okay now in terms of a working solution.  He replaced the MOSFET with a diode.  The anode would be directed to the 3.3V side and the cathode to the 5V side.  When the collector is 5V the diode will be off.  When the collector is 0V the diode conducts and roughly 0.7V will be present on the 3.3V side, which can generally be interpreted as logic 0.  The purpose of this post is to try and understand why the original method failed.

Thanks for your interest and sorry for the "chain" of messages, I didn't want to start with a too long explanation as the issue here came up from my project but could also apply to others.
Basically, I have a 24V power source that may disappear so I need a signal to tell me so. In the very first discussion, I came up with a 2N3904 which base is controlled by that 24V signal and whose collector is simply pulling down to 0V when the 24V is there. With the appropriate 3.3V pull up, I then got a signal that tells me when 24V is there (0) or when it has gone (1) and thus have the OrangePi react to the power being gone by properly shutting down while 5V capacitors are being slowly emptied.
Sadly, this was not enough because OrangePI needs to be fully disconnected for it to properly restart after shutdown, and so the subject of my second message was to find a way to have a more complex set of signals to control this.
Using a PIC16F628 that was lying around, I was able to write the proper control program, but because that PIC uses 5V power, the OrangePI is on 3.3V, and both need the "24V is gone" signal, hence my need for a "level shifter"

That final need led me to the research here, with the application note I mentioned and the puzzling fact that it appears not to work. As dang74 said, I was able to get a working situation with a simple 1N5819 diode but there is this part of my mind that wonders why the setup from the application note failed in real life and even does not simulate properly at all in LTSpice.

Yes, 24V is steady, not fluctuating at all, the purpose of all this really is to detect total absence or presence of a DC 24V  source.

The application note is mentioning the BSS88 as it have a low enough VGS(th) but I had a hard time finding a spice model for it, so maybe the lib I found is not appropriate for the symbol I chose in LTSpice

I am not sure if there is a minimum knee current to overcome for the substrate diode within the mosfet to 'conduct.'  You could try repeating your simulation but changing the pull up resistor from 4.7k to 470 ohm.  If there is no change then it's fair to say that the model doesn't include the internal diode.

This is the problem with these types of circuits; they're always 'on the edge', requiring one to hope that 3.3V really is 3.3V and not (say) 3.1V, and that the MOSFET can switch on sufficiently (and that's not even considering that there will be a further 0.2V or so across the BJT, could be replaced with another MOSFET but it's still ugly).. It's just not needed when there are better ways to do things that are not so critical of components and voltages. Sometimes worth re-thinking bits of the solution, rather than (say) just use a 5V microcontroller if it can run at 3.3V.

BSS88 is ancient/obsolete, so if you have these, are they from Amazon/AliExpress? In which case, they may be something completely different but deliberately marked wrong. That could explain why your real-life circuit doesn't pull as low as desired.

You could try a different low Vgs MOSFET that isn't obsolete, e.g. BSS138 is a reasonably choice, and plenty in stock at Farnell etc. 

Regarding the different results with LTspice, it may indeed be an issue with the model or how it was pulled in. Might be worth trying a different model if LTspice has any built-in with low Vgs, otherwise googling 'BSS138 spice' there was a downloadable model from one of the manufacturers online.

I get that, but personally I don't like working like that. I'd rather know the background, in case things are being done unusually and if a different path should be chosen.

In this case for instance, there's actually no need for this level conversion, if the microcontroller was run at 3.3V; and even if the level conversion is needed if the microcontroller can only run at 5V, there's no need to do a bidirectional implementation here (with all the shortcomings of this type of implementation), so things can be simplified further.

Fair point.  It's nice to have the full picture sometimes... and some posts (not this one) are so lacking in information it is next to impossible to offer any guidance.