Seeking Guidance on Providing Filtered Data Access to Specific Branches for Power BI Reporting

Dear Forum Members,

I hope this message finds you well. I am seeking advice and guidance on a data access issue that I am currently facing, and I believe this community’s expertise may be instrumental in finding a solution. I apologize in advance for the length of this post, but I want to provide a comprehensive background on the issue.

Background: I am responsible for managing data access and reporting for different branches, each of which represents a distinct company within our organization. These branches have expressed a need for direct access to specific data for use in Power BI, enabling them to create their own custom reports and charts.

Challenges: My primary challenge lies in providing these branches with filtered data that corresponds to their specific branch, ensuring data privacy and security. My goal is to empower them to create meaningful reports while maintaining strict access controls.

Current Approach: I have attempted to address this challenge by developing a data API that can filter data based on the branch. While this has been successful on the data preparation side, I am struggling to understand how to connect this filtered data source to Power BI.

Key Questions:

  1. How can I effectively provide filtered data access to specific branches through Power BI?
  2. Are there best practices or recommended methods for achieving this without compromising data security and integrity?
  3. What is the process for connecting filtered data from an API to Power BI while ensuring that branch-specific access is maintained?

I would greatly appreciate any insights, experiences, or suggestions that forum members can provide regarding this complex issue. Please share any relevant resources or step-by-step guidance, as I am dedicated to finding a secure and efficient solution that empowers our branches to harness the power of Power BI while adhering to strict data access controls.

Thank you in advance for your valuable input, and I look forward to engaging in a fruitful discussion within this community.

I have not read all details of your post yet but at first glance I would think that one direction to look would be

You define a view with an aggregation pipeline. With a $match stage you make sure only documents from a given branch is visible in a given view.

As for

I do not know if it is a best practice but you may define user’s roles that only give read access to the branches views. You may even REDACT some fields to hide confidential information.

No. It will not work. I can’t see any ability grand access for power bi for specific user/private keys etc. Also it look like I have to create same view for each table for each branch. and each time when I create new model I have create for each branch new views.