topic Re: One semantic model for many clients: pivoting key-value data blows up my fact table in Data Engineering

One semantic model for many clients: pivoting key-value data blows up my fact table

robertozsr — Wed, 22 Apr 2026 16:25:07 GMT

Hey all, looking for some sanity-checking on my dimensional model.

Context: We had separate Power BI Desktop files per client — each was a copy of the same model, tailored per tenant. We've now moved to Fabric and I want a single semantic model filtered by client_id, so every client only sees their own data. Tailored dashboards can still be built on top of the same model when a client asks for something custom.

My v1 was splitting the model per client. With help from the community I realized this gets messy fast — we have a lot of clients using this dashboard.

The problem: When we onboard a client on our platform, we store their data as key-value pairs, and the key names vary per client. When I pivot the long format into wide, I end up with ~600 columns in fact_check. I know columnar storage handles sparsity fine in terms of memory/compute, but a 600-column fact table in Power BI still feels wrong — the Fields pane is unusable, and every report builder has to scroll through a sea of null columns.

This is exactly why v1 had me filtering by tenant_id before pivoting — at that point the irrelevant keys were rows, not columns, so I could drop them cheaply, and each tenant ended up with a dense table of just their own keys. Clean, but meant one table per tenant, which doesn't scale.

Where I'm stuck: I'm a junior, started as a data consultant, and I don't want to over-engineer because I need to show something to stakeholders soon. This is v2 of my semantic model.

Any tips or patterns for multi-tenant semantic models where each client has a different set of metric keys? How do you handle the wide-vs-long tradeoff in Fabric without either (a) ballooning your fact table to 600 columns or (b) splitting the model per tenant?

Appreciate any pointers.

Re: One semantic model for many clients: pivoting key-value data blows up my fact table

Tamanchu — Wed, 22 Apr 2026 23:32:52 GMT

Hi @robertozsr,

Classic multi-tenant EAV challenge and the good news is: don't pivot at the Lakehouse level. That's what's causing the blowup.

What's happening
When you pivot all key-value pairs across all tenants into columns, every client's unique keys become columns for everyone mostly NULL. That's the sparse wide table anti-pattern, and it gets worse as you add clients.

Recommended pattern: keep EAV, enforce boundaries at the model layer

Keep your data normalized in the Lakehouse :
FactMetrics (tenant_id, metric_key, metric_value, date)
DimMetricKeys (tenant_id, metric_key, display_label)
DimTenants (tenant_id, tenant_name)
Then in your semantic model :

Apply Row-Level Security (RLS) on tenant_id each client only sees their own rows, so they only see their own keys
Use DimMetricKeys as a slicer maps raw key names to human-readable labels per tenant
Write DAX measures using SELECTEDVALUE('DimMetricKeys'[metric_key]) to slice dynamically

This way the "pivot" happens at the report layer, not in storage no blowup, no sparse columns.

If you need a physical wide table (performance reason only)
Create per-tenant SQL views in the Lakehouse SQL endpoint, pivoting only that tenant's keys. Use those views as DirectQuery sources in the semantic model.

As a junior consultant : start with the EAV + RLS approach. It's clean, scales well, and avoids maintaining per-client pivot logic. Only add complexity when you hit a real bottleneck.

More on RLS in Fabric: https://learn.microsoft.com/en-us/fabric/security/service-admin-row-level-security

Hope that helps!

Re: One semantic model for many clients: pivoting key-value data blows up my fact table

v-hjannapu — Mon, 27 Apr 2026 06:42:39 GMT

Hi @robertozsr,

I would also take a moment to thank @Tamanchu , 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.

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.

Regards,
Community Support Team.

Re: One semantic model for many clients: pivoting key-value data blows up my fact table

robertozsr — Tue, 28 Apr 2026 09:08:15 GMT

Thanks for the detailed and insightful answer! I will look into it!

Re: One semantic model for many clients: pivoting key-value data blows up my fact table

robertozsr — Tue, 28 Apr 2026 09:27:18 GMT

After a more careful reading: I think this is the correct solution. Thanks a lot. This really helps a lot! I will go on with trying implementing it!

Re: One semantic model for many clients: pivoting key-value data blows up my fact table

robertozsr — Fri, 01 May 2026 08:44:23 GMT

@Tamanchu Hello! I am applying your suggestion. I have three facts (metrics, business rules, other_attributes) and created 3 different dimension that in the semantic model are related to those fact.
Now, because I am still in developing phase I am directly filtering on the page, for different tenants to test. And indeed base on the tenant I see different dim.keys in one slicer, and then when I select one of those key, another visual shows me the value from the fact! So thanks a lot!

But I have two questions:
-

Write DAX measures using SELECTEDVALUE('DimMetricKeys'[metric_key]) to slice dynamically -> I did not really understood why i need this and what it does where should I place it etc. To me when I select the key from the slicer dynamically already changes what i see in the visuals for the value. I created this measures you told me and applied to my visuals that should so the metric value, but nothing shows up. I am probably missing some understaing.
Reagarding the RLS, I will implement it later the concept is the same as filtering per tenant_id directly in the report? At the current state we create the dashboard in powerbi dekstop, in power query filtering at source level with tenant-id, then publish the dashboard to the client workspace, get the link and display for them. And then again creating another dashboard locally and redo the same process. Of course that is why we are migrating to Fabric to have something more dynamic and scalable. And in this actual situation I just create 1 report, and then implement this RLS, but not sure if I will still have to create more reports just copy past and change then rls, or because of this RLS one report will be enogh... That is for later but if you have some pointers already would be nice. I have checked your link for RLS!

Thanks a lot!

Re: One semantic model for many clients: pivoting key-value data blows up my fact table

Tamanchu — Sat, 02 May 2026 22:41:57 GMT

Hey! @robertozsr, glad it's starting to work 🙂

On SELECTEDVALUE vs. Simple Filtering

You're right, the slicer already filters the rows. The real power of SELECTEDVALUE is turning a filter selection into a scalar value (a single piece of data) that you can reuse inside other logic.

While a simple
SUM(FactMetrics[metric_value])
works when a relationship is in place, your explicit pattern is great for debugging.

If nothing shows up, the first thing to check is filter propagation in most cases, it's a relationship issue.

Keep in mind : SELECTEDVALUE returns BLANK() when multiple values are selected, so it's best used with single-select slicers or with a fallback value.

The Composite Key Challenge

Power BI doesn't support relationships based on multiple columns (e.g., tenant_id AND metric_key). If your data grain depends on both, you have two main paths:

The Robust Way
Create a composite key column (tenant_id & "-" & metric_key) in Power Query or your SQL upstream, then build the relationship on that column.

The DAX Way
Use TREATAS to propagate filters virtually (especially useful when working with disconnected tables or when a physical relationship isn't possible).

On RLS (Row-Level Security)

In real-world scenarios, it's better to use a security table instead of directly comparing tenant_id to USERPRINCIPALNAME().

Example :

Security table : UserEmail | tenant_id

RLS rule :

Security[UserEmail] = USERPRINCIPALNAME()

Then relate :

Security → Tenant → Fact

This approach is more scalable and allows one user to access multiple tenants if needed.

Important

RLS is only enforced for users with Viewer permissions.
Admins, Members, or Contributors can bypass it due to their elevated permissions (e.g., direct access to the semantic model).

So in multi-tenant scenarios, make sure end users are assigned as Viewers.

Here is the official documentation for deep diving into RLS :
https://learn.microsoft.com/en-us/fabric/security/service-admin-row-level-security

Re: One semantic model for many clients: pivoting key-value data blows up my fact table

robertozsr — Mon, 04 May 2026 07:38:27 GMT

@Tamanchu

Hello! Thanks again for the clear explanation and detailed answer! I will look into it, and if I have question I will come back to you. Thanks a lot!!!