Bridge tables, shared dimensions and analyzing across two fact tables

Power BI Data Model
1

Hello
Below is my original model

model_1
model_1.png811Ă—502 26.9 KB

Fact 1 - (Budget table)
The budget table is joined to my Date table via a virtual relationship (as shown in course video) . The granularity is by month

Fact 2 (Account entry table)

Both tables share the two dimensions (with red square around them) and the date table The two dimension tables to the left are only linked to the Budget table.

With this model in place, I added selected columns from the all four dimensions including the MonthYear column from the date table to a table visual. The measure is coming from Fact #2 table.

Problem : I had double entries showing when the columns from the left two dimensions (linked to Fact 1) were selected. I thought that because both tables were sharing 3 dimensions, I could still be able have additional dimensions added that are not “shared” between the two.

Am I missing something or is it that all dimensions must be “shared” inorder for PowerBI to drillacross the two fact tables and show the correct results?

With incorrect results, I ended up redoing my model by adding a bridge table - the one on the top) Screenshot below. Now the results are filtering correctly.

model_2
model_2.png841Ă—624 28.5 KB

Question : Do I need to add a bridge table (and change the relationship direction to bi-directionaly for one of the dimensions) for PowerBI to filter across the two fact tables correctly? Do all dimensions need to be shared between the two fact tables inorder for the results to show correctly?

Thanks

2

This is a tough one to answer because your model can totally depend on what you need to showcase in the report. So to say that one way is the way to go just isn’t possible because it can depend on so many things.

You just need to clearly think about what context is be generated by your model when you place certain dimension into a report page or visual.

The results or calculations being completed by your measures are being determined by whatever filters and relationships you have in your model. Just think really deeply about each individual result and try to think what your model is doing in the background to generate that particular result.

This is the only way to truly know what type of model you require.

Thanks
Sam

1 Like
3

Hi @sam.mckay
Thanks for your reply.

You are right, I’m reviewing my model and report requirements again.

Thanks