Need Help Creating Generic Measures Based on Dynamic Table Selection by Organization

[raaan12]

Hi Power BI Community,

I'm currently building a dashboard that will later be embedded in a web application. The challenge I'm facing is that my data model includes multiple tables with different names but the same structure. Each table corresponds to a specific organization and follows a naming pattern: ORGANIZATIONCODE_booking.

For example, I have three organizations with codes X, Y, and Z, which means I have three different tables: X_booking, Y_booking, and Z_booking. All of these tables have the same schema but contain organization-specific data.

When a user logs into the web app, we can detect their organization code from their profile. Based on that, I want the dashboard to dynamically pull data only from the relevant table (e.g., if the user is from organization X, it should query X_booking only).

I've already imported all tables and built my semantic model. Now, I want to create generic measures that can adapt to the organization context dynamically, without having to duplicate logic for each organization manually.

Is there a recommended way to handle this scenario in Power BI? How can I write measures or queries that adapt based on the organization code so that only the corresponding data is used?

Any suggestions or best practices would be greatly appreciated!

Thanks in advance!

[v-mdharahman · Accepted solution]

Hi @raaan12,

Thanks for reaching out to the Microsoft fabric community forum.

It looks like you are trying to developing a Power BI dashboard that will be embedded in a web application. The application will support multiple organizations, and each organization has its own dataset stored in a separate table following a consistent naming convention. Power BI's current architecture doesn’t support dynamic table binding based on a runtime parameter. So unless the collections can be unified efficiently at the source or via an API layer, the options are either maintaining separate models per org or building a scalable back-end abstraction to serve the right data from a single logical table.This kind of setup, where each organization has its own table with the same schema, is a common pattern but can introduce complexity in Power BI, especially when trying to build a single, maintainable semantic model that supports dynamic behavior based on user context.

Given your setup in MongoDB with one collection per organization (e.g., X_booking, Y_booking, etc.), and the fact that each has the same schema but different data, I can see why combining everything into a single table isn’t working for you especially with the performance and maintenance challenges you mentioned.

You're right that Power BI doesn’t currently support dynamic switching between tables based on a runtime parameter or user context. DAX can’t dynamically reference a table name, it has to be hardcoded. That’s the main blocker in trying to create a “generic” semantic model using separate tables.

Since you're using MongoDB, one option might be to introduce a lightweight middleware layer (e.g., an API or a database view) that consolidates data from the relevant collection at query time based on the organization code. This would allow Power BI to always connect to a single logical table, and that table would return only the data for the active organization. If you’re embedding, you can pass the organization code as a parameter to the API. This avoids bringing all data into Power BI and helps with performance and scalability.

You can also use Power BI parameters, and while they don’t work dynamically in DAX, you can use them to control which table or query gets loaded but this only works at refresh time, not at runtime in the embedded view. It’s more useful in a desktop/development scenario or when triggering dataset refreshes via the REST API.

I would also take a moment to thank @Deku, for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.

If I misunderstand your needs or you still have problems on it, please feel free to let us know.  

Best Regards,
Hammad.
Community Support Team

If this post helps then please mark it as a solution, so that other members find it more quickly.

Thank you.

[Deku]

Why not use RLS and a single table?

[raaan12]

Thanks for your reply!

Actually, in my case, the data is stored in MongoDB with a separate collection for each organization — that's how we differentiate the data. 

I’ve tried combining all these collections into a single table in Power BI to apply RLS, but this approach caused significant performance issues and latency, especially as the data grows. Additionally, every time a new organization is added, I would need to manually update the dataset to include its collection, which is not scalable in the long term.

That’s why I’m looking for a more dynamic or generic way to handle this scenario based on the connected user’s organization.

Appreciate any further suggestions!