Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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 :
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 ?
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.
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 😋
Why would that not be efficient? That is essentially what relationships are accomplishing. It would solve this issue 100%
@Anonymous
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
@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.
@Anonymous
Sorry!!..Incorrectly tagged you in the previous post.
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...
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.
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
87 | |
87 | |
87 | |
67 | |
49 |
User | Count |
---|---|
135 | |
113 | |
100 | |
68 | |
67 |