Multiple Work Profile in one Android device

You can have only one work profile on one device (please correct me if I'm wrong) but depending on your MDM used, i would recommend to look into something like "shared device" where the relevant user logs in (on a login page/kiosk screen) and when logged out all relevant and sensitive data is being removed,

But in that case it's not a private (BYOD) device anymore, has to be then a fully managed device (COBO).

Just an example from our MDM: https://www.youtube.com/watch?v=mMxxd8KxGUQ

This is an arbitrary limit set by Google back when devices were running around with 512mb RAM and needs to change. But until then, as Moombas says it's only possible to have one work profile at a time.

It is currently not possible to have two Work Profiles.
The data on a devices with a work profile is separated via Android users. The personal area is the system/primary user and the work profile is a secondary user. However, I suspect that the work profile is a special case among the secondary users. In addition, a second work profile could not currently be displayed via the interface.

If multiple people are supposed to work with one device, Moombas' suggestion is a good one.
However, the options vary greatly depending on the MDM used and which device manufacturer (OEMs) is involved.

One option are kiosk (/work managed) devices, where the app selection is set and the app data is reset after a certain time (or a logout).
Alternatively, there is also the multi-user functionality in Android, which may also be supported by some MDMs. However, multi-user is an optional feature for OEMs. Samsung, for example, only offers this feature on tablets.

As far as I know, this limitation has not been removed yet.

It is very unfortunate because Work profiles have seen a lot of adoption in businesses, and some even restrict access to their Google Workspace information from a work profile exclusively.

If you work at multiple companies (or provide services to multiple ones that create an account for vendors access), the max one Work profile per Android device is a serious drawback, and cancels the whole BYOD paradigm benefits.