[Infra] Show the correct error message when the request to/api/metrics/source/ fails

[jennypavlova]

Kibana version: 8.15.x

Describe the bug: When the request to /api/metrics/source/ fails we show the wrong message regarding the remote cluster connection - the correct message should be related to the source error

Steps to reproduce:

1. It's not easy to reproduce - the only easy way I see is to "fake" an error API response (4** / 5**)
2. Then go to Metrics Explorer
3. A wrong error message is displayed:

Expected behavior:
The error message should be based on the error thrown

To fix that we need is: first to return the source errors if any and then show the remote cluster error (only if the request is successful and no other errors are present)

[elasticmachine]

Pinging @elastic/obs-ux-infra_services-team (Team:obs-ux-infra_services)