Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Frequent Visitor

One table to rule them all

Hi there,


I have an issue with my data model. Bassicaly, I need to filter all my report according to the row filtered from one main table.


In my example, I have 3 dimensions and 1 fact table :

- DimSource

- DimClient

- DimProdut

- DimSales


The main table (DimSource) should be able to filter every table since a column named "SourceId" is available in every other tables. I made sure that all the data don't cross from one Source to an other :

Source - ex.PNG



However, I'm not able to make it work, even though the data don't cross. Here's my model:



 I've tried using some RLS but I couldn't make it work.


Any ideas ?

Memorable Member
Memorable Member



You have to merge DimSource and Dimsales to filter the measure from sales by login. Also, to filter the dimensions, modify the filtering as bidirectional between client -Sales and Product-Sales.


No need to have the source ID in Product and Client.


If this helps, mark it as a solution

Kudos are nice too.



Connect on LinkedIn
Not applicable

Why not merge all of these onto a single Query? Or make 2? Seems kind of overkill to need that many relationships when there aren't many unique columns

This is only an example to explain my problem/need.

My real data model contains about 20 dim tables and 3 fact table soooo... I doubt this would be efficient if I merged everything in only one table 😋

Not applicable

Why would that not be efficient? That is essentially what relationships are accomplishing. It would solve this issue 100%



Based on the model screenshot, you cannot make the relationship from source to client as active because of circular reference. Ideally, there should be only one active path, so that power bi will know which path to travese from Source to sales via Product. If all of them is active, it doesn't know it should traverse vis product or client. Thats the reason behind allowing only one relationship as active and rest as inactive(see USERELATIONSHIP in DAX).


If this helps, mark it as a solution

Kudos are nice too


Connect on LinkedIn
Not applicable

@VasTg Exactly what I was getting at. I think you misunderstood who I was disagreeing with.



Try this...

1. Create a bridge table(all combination of client,Product and source id)

2. Define relationship from source to bridge(1 to M - single direction)

3. Define relationship from Client  to bridge(1 to M - bidirection)

4. Define relationship from Product to bridge(1 to M - bidirection)

5. Join the bridge to facts(maybe M to M - single - bridge to facts)


Hopefully this should work.

Let us know.



Sorry!!..Incorrectly tagged you in the previous post.



Connect on LinkedIn

This could do the trick in the example but it won't work because I would need one gigantic bridge table.

My real model kinda looks like this (and will grow in size in a few weeks because there are still some tables and rows missing):



Besides, I wouln't be able to see a table with ProductName, Fullname and Date. Which is kinda annoying for a fact table 😁

That's the main issue... I need to filter the dim tables according to the source selected.

I understand this kind of relashionship shouldn't be allowed. However, according to the data I have in my model, this shouldn't be a problem and this would make sense. This is what I'm trying to achieve:




Therefore, I've been trying to use RLS to create a filter in every dim table without creating the relashionships using the DimSource table. However, I didn't manage to do it...



Did you make the filter as bidirectional between sales to client and sales to Product? This should give the result you need. Test it out before merging Source to sales.



Connect on LinkedIn

Bi-directionnal wouldn't work because I have more than 1 fact table in my real model 😉

Just try to make it active from the property and check what error it gives. I faced the same issue when I tried joining one table by (say ) source not by source Id. I deleted all such join and created all join on source IDs.


Also make sure there are no two paths, which not visible here in diagram.

Helpful resources

PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors