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!
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!
Thanks for the detailed and insightful answer! I will look into it!
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!
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!
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-securityHello! 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!!!
