INTERSECT between two fact tables using a bridge in-between (only connected to one Fact table)

[Anonymous]

Hello everyone,

Quick question, I have two Fact tables which even if I could, I wouldnt be able to join due to circular dependencies, so I need dimensions that filter 1 Fact table to also filter the 2nd..

I built a bridge table that connects the 1st Fact table to a bridge table (Via Fact1Key), where I set the filtering to (Bridge)1:M (Fact) and the filtering to both tables (as I wish Fact table to filter the bridge) and I am using VS 17.

Then I created a measure that makes a sum in one column on Fact Table 2 using an INTERSECT between Fact2 & Bridge and Fact2Key.

I get a number, but as soon as I bring dimensions that are in Fact1 all turns to blank.
And the dimensions from Fact1 indeed filter the bridge table, but it does not seem to work then on my measure on Fact2.

Is it not possible to do what I do the way I am doing? If not, how could I achieve this (knowing about my circular dependency issue).

Thanks!

[v-diye-msft]

Hi @Anonymous ,

It's not needed to create bridge table to manage relationship now, you can refer to the document:https://docs.microsoft.com/en-us/power-bi/desktop-create-and-manage-relationships

But that's not the point, could you please show me your formula used and the simple data ? dummy pbix is much more preferred.

[Anonymous]

Hey,

Thanks for the reply, I actually should have answered to myself.
Anyway, the behaviour works as it should...So in case anyway has doubts, INTERSECT or whatever Function one uses to create a virtual relationship indeed brings the filtering across.

The problem was at the source, the keys were not properly matching...

Anyway, in my case I do need a Bridge Table, in my case its an hybrid between Bridge & Mapping Table as it holds the mapping between KeyFact1 and KeyFact2.. I made it Many to One From Fact1 and then I used the Intersect Formula between Fact2 and Map/BridgeTable and was able to bring the filter context applied to Fact1.

Thanks!