cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Angith_Nair
Resolver II
Resolver II

Modelling + Relationship Issue

Here there are 4 tables Company, Fact Table, Dimension 01 & Dimension 02. The relationship are as follows:
1. Company[Company ID] -> Fact Table[Company ID]
2. Dimension 01[Field 1] -> Fact Table[Field 1]
3. Dimension 02[Field 2] -> Fact Table[Field 2]
Now my requirement is I want to filter Dimension 01 & Dimension 02 tables based on Companies. If I will delete

Company[Company ID] -> Fact Table[Company ID] relationship and then create relationship between

Company[Company ID] -> Dimension 01[Company ID]  & Company[Company ID] -> Dimension 02[Company ID] then it will create circular dependency. So what will be the best approach to achieve this requirement?
Thank you in advance.

Angith_Nair_0-1611839332394.png

 

2 REPLIES 2
StefanoGrimaldi
Solution Sage
Solution Sage

hey, 

wiithout seeing the actual table wont be a much specifuc answer, for what you show on the diagram you can merge dimension table 01 and 02 into one containg those company id field 1 in one column and field 2 in another via power query merge function, delete the relantionships and create a relantionship between comapany and the new dimension table merged and them those to the fact table (making sure that dimension contains all Company ID even if others column keep blank data). 

another approch would be making both dimension 01 and 02 bidirectional relantionship, not optimal for relantionship between fact and dim table but will do the trick. 

 

if this helped please give some kudos and mark as solution for others to find, 

for a better answer I would recommend posting a PBIX file with this case with dummy data on it to check and get better solution. 

@Angith_Nair there is one point which I would like to highlight for the option 1 suggest by @StefanoGrimaldi - if you merge the dimension tables into a single table, you will not be able to have an active relationship between the dimension & fact tables for each dimension and this might be a probllem for you when you use the dimensions as filters.

 

@StefanoGrimaldi please guide in case I have understood this incorrectly.

 

Thanks,

Vinay

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors