Forum Discussion
One semantic model for many clients: pivoting key-value data blows up my fact table
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!
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
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!!!