Creating a matrix in BI that shows the workspace names, their respective reports, and the users

[Target]

In my organization, I've created multiple workspaces and published numerous reports within each workspace.
Now, I need to create a matrix that displays the workspace names, their respective reports, and the users who have access to these reports.
How can I achieve this?

[Target]

Hi,

It Worked,However my organizations expects that in excel Sheet,So i did it in excel.

[Poojara_D12 · Accepted solution]

Hi @Target 

To create a matrix displaying workspaces, reports, and user access in your organization:

Steps:

1. Export Workspace and Report Data:

   • Use the Power BI Admin API to retrieve information about workspaces, reports, and their permissions.
   • APIs to use:
     • GetGroupsAsAdmin for workspace details.
     • GetReportsInGroup for reports in each workspace.
     • GetDatasetUsersAsAdmin for user access information.

2. Combine Data:

   • Merge the workspace, report, and user data using tools like Power Query or Power Automate.
   • Ensure relationships between workspace names, reports, and users.

3. Build the Matrix:

   • Import the combined dataset into Power BI Desktop.
   • Use a matrix visual to display:
     • Rows: Workspace and Report names.
     • Columns: User names.
     • Values: Access roles (e.g., Admin, Viewer).

4. Publish and Update:

   • Publish the matrix report to a Power BI workspace.
   • Schedule regular refreshes to keep data updated.

Requirements:

• Power BI Admin Role: Necessary to access the required APIs.
• Access to Tools: Power Automate, Power BI Desktop, or other data manipulation tools.

Would you like guidance on using specific APIs or building the dataset?

Did I answer your question? Mark my post as a solution, this will help others!

If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂

Kind Regards,
Poojara
Data Analyst | MSBI Developer | Power BI Consultant
Please Subscribe my YouTube for Beginners/Advance BI Concepts: https://youtube.com/@biconcepts?si=04iw9SYI2HN80HKS