How to use credential for DirectQuery chaining without giving end-users access to upstream data?

Hi julsr ,
Thanks for reaching out to Microsoft Fabric Community.

The behavior you are seeing is expected.

With DirectQuery chaining in Power BI, queries from a downstream semantic model are always executed using the identity of the report viewer. Because of this, every user consuming the downstream model must have at least Read permission on the upstream dataset.

Reference:

Use composite models in Power BI Desktop - Power BI | Microsoft Learn

It is not possible to use a service principal, managed identity, or fixed credential for DirectQuery queries between semantic models, and a downstream model cannot return data to users who have no permissions on the upstream model. Chained semantic models are not designed to act as a security boundary or proxy.

Additionally, hiding or renaming columns in the downstream model does not secure them in this architecture, since users with permission on the upstream dataset can still access it directly.

Reference: Working with a composite model based on a semantic model 

To securely limit column visibility while keeping data live, security must be enforced below the semantic model layer, such as at the data source itself (for example using database level row or column level security), or by materializing a curated data layer and exposing only that curated data through a single semantic model. Chained semantic models are not designed to propagate security rules.
Reference: Working with a composite model based on a semantic model 

Hope this helps. Please reach out for further assistance.
Thank you.