One table to rule them all

[Marfield]

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 ?

[VasTg]

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

[Anonymous]

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

[Marfield]

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]

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

[VasTg]

@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

[Anonymous]

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

[VasTg]

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

[Marfield]

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 😁

[Marfield]

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

[VasTg]

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

[Marfield]

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

[amitchandak]

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.