Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
SuperFiets_
Helper I
Helper I

Filtering semantic model for end-consumer

Hi everyone,

 

We're implementing an end-to-end solution using Microsoft Fabric, including Dataflows Gen2, Pipelines, Lakehouses, and Warehouses. However, we’re facing a challenge and need your advice regarding the following.

 

Our Workflow:
1. Ingesting data with Dataflow Gen2 for multiple clients into a lakehouse.
2. Enrich this data in another lakehouse.
3. Load the completed data into a warehouse.

4. Creating a semantic model with relationships and measures based on the final warehouse.

 

The Challenge:
We need to extract data for a single client into an Excel template, despite having multiple clients' data in the warehouse. Ideally, we want to connect the Excel template to the semantic model (containing measures and data model) linked to the final Data Warehouse and filter this model to load only the data for the selected client into Excel. We're using the "Analyze in Excel" function with the semantic model or a connection string.

 

Previous Approach:
Previously, we connected Power BI Desktop to a SQL connection string, filtered client data in Power Query, and published the filtered data to the online service. However, this approach isn't scalable for our growing number of clients. Since we can't update the semantic models when the Power BI Desktop template changes. 

 

Options Considered:
1. Direct Connection with Slicers: Connecting the Excel template directly to the semantic model and using slicers to filter client data. This approach loads all rows, affecting performance and potentially exposing other client data.
2. Old Approach: Connecting to the data warehouse, filtering in Power Query, and publishing a separate semantic model. This is not ideal as we want a centralized semantic model from the warehouse.
3. VBA or MDX Queries: Exploring VBA or MDX queries on the connection string, but due to limited experience, I couldn't get it to work. Something like SELECT * FROM [Client] WHERE 'Name' = 'FilteredClientName' in the command text would be something that would be enough.

 

Question:
How can we filter a semantic model to show data for only one client before connecting it to Excel with a connection string? Any guidance or alternative approaches would be appreciated. At this point, open to anything.

 

Thank you for your help!

1 ACCEPTED SOLUTION
miguel
Community Admin
Community Admin

I've moved this entire topic to the Power BI forum as it's more closely related to Power BI specific inquiries and behaviors. 

The answer or solution, as previously stated by others, is implementing your own RLS logic. That would make sure that folks only get to access exactly the data that they need to access and nothing more.

How you could implement such RLS logic / policy is something that the Power BI forum would be far better suited than the Dataflow Gen2 forum.

View solution in original post

8 REPLIES 8
frithjof_v
Super User
Super User

Could you give each client just a CSV file or an Excel file?

 

Or the client needs to access the semantic model directly?

 

 

How complex is the Excel file you are creating?

 

If the client only needs a CSV file or Excel file, maybe it's possible to use Notebook or Power Automate to loop through your list of clients, query the semantic model data (or Lakehouse data directly) based on client identity, and create a CSV file or an Excel file for each client.

 

With Power Automate, I guess you could use a template Excel file and create a copy for each client and fill with the client's data by querying the Power BI semantic model from Power Automate, using the client identity as a query variable. Looping through the list of clients to create a copy for each client.

 

 

--------

 

 

Anyway, this is an interesting topic and I guess many companies could be interested in knowing how to solve such cases.

 

Hoping someone else have some viewpoints and suggestions on this topic 😀

The client unfortunately needs to access the semantic model directly. So the only way to do this is to use analyze in Excel feature in Power BI. The templates are also quite complex and have quite a lot of pivot tables, summaries etc.

 

I think we will approach it partly how we used to. Connecting the semantic model directly to a Power BI Desktop file, use query folding to get 1 client and then publishing the filtered semantic model. That way the data is safely filtered and can't accessed with some tinkering. Would involve building a PA Desktop bot to set up all the files, but that wouldn't be too much of an issue.

miguel
Community Admin
Community Admin

I've moved this entire topic to the Power BI forum as it's more closely related to Power BI specific inquiries and behaviors. 

The answer or solution, as previously stated by others, is implementing your own RLS logic. That would make sure that folks only get to access exactly the data that they need to access and nothing more.

How you could implement such RLS logic / policy is something that the Power BI forum would be far better suited than the Dataflow Gen2 forum.

Unfortunately this won't work for the situation I have. Your answer or the others are not the solution to this topic. 

 

However since you already decided so, I will look into other options. Thanks.

frithjof_v
Super User
Super User

Have you considered using RLS (Row Level Security)?

Yes we have, but for our scenario, I am not sure it can work.

Let's say we have 100 clients, and about 5 teams. We'd create RLS for the 5 teams, but that still means they get to see 20 clients each. 

 

Do you think another approach would be to have 100 clients, each one having it's own O365 group. When adding users to the O365 groups, would they somehow only get to see the data for the specific client? Even when users are added to multiple O365 groups. For example user A would be added to O365 group 1, 2 and 3. Would user A still get to see all items from 1 to 3? 

 

The final step for us, is connecting the data to Excel. So it's very important that user A only gets to see the data for item 1, when we deploy the Excel templates for item 1.

I think the RLS would only work if each user only has access to one client.

 

So I think if you have 100 clients, you would need a separate user for each client.

(I.e. 100 users).

 

I'm not a Microsoft 365 administrator so I'm not sure if that is a good approach. But I guess it could be problematic, because it means you would need to create a "dummy user" or "service user" per client, unless you already have a user per client.

I guess creating such "service users" could create governance risks related to authentication. But I don't have enough knowledge on that subject (user account management in organizations) to say something for sure.

 

So I'm not recommending it.

I'm just saying if you were to use RLS, I guess you would need separate users for each client.

Thank you for your reply.

 

I also think this problem will occur, I have 100 clients, and also 100 seperate O365 or security groups per client. But I only have about 20 users, that are spread accross the groups. Some users can occur (a lot) more than once in group.

 

So I think that that one users will still see all the data for the groups he is part of and not just the one we want to show.

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.