Groups and sub-groups in one report

Hi,

We have an Accounts table in SQL Server where each account has a unique ID. We have Power BI Premium Embedded, where we do not apply any RLS within Power BI. However, when a client logs into our application, the AccountID is filtered based on the user's credentials and selected account, allowing them to see only the reports linked to their account and not to others.

Now, I want to create a new report for grouped accounts. For example, an account can have multiple sub-group accounts, and we want to display all of them in one report. If I’m correct, I should add a new column to the Accounts table for a GroupID, so both the group and sub-group accounts will have a unique GroupID.

How should I handle the filtering aspect? For instance, if the UI filters the account to AccountID=1, the user should be able to see reports for any accounts with the same GroupID.

Do you have any ideas on how I can implement this type of group reporting?

Thanks a lot!