Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get 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

Reply
Marfield
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:

Source.PNG

 

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

 

Any ideas ?

12 REPLIES 12
VasTg
Memorable Member
Memorable Member

@Marfield 

 

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
Anonymous
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 😋

Anonymous
Not applicable

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

 

Connect on LinkedIn
Anonymous
Not applicable

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

@Marfield 

 

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.

 

 

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):

model.PNG

 

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:

 

result.png

 

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...

@Marfield 

 

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.

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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