Forum Discussion
How Would you Create dimention Model In this Case
Let's say there are three levels of object and there could be two possible way of relationships
Strategy -> Project -> securities -> Facts
Strategy ------------->securities -> Facts
There is a common table currently storing securities transactions. PowerBI doesn't allow me to create these two active relationship concurrently. Have you guys seen anything like this before and what's the best way to deal with it?
What i can think of is to break Facts into two table one for each kind of realtionship. But this would make reporting incomplete. For example if i want a table having the total value of the two. Thank you very much.
1 Reply
Hi Anonymous
Given what you've described, I would be tempted to join Strategy, Project and Securities into a single dimension table "Securities".
The joined Securities table would follow this structure:
Strategy Project Securities Source table Strategy Key Strategy Cols Project Key Project Cols Securities Key Securities Cols Strat_001 <…> Proj_001 <…> Sec_001 <…> Project nonblank Strat_002 <…> Proj_002 <…> Sec_002 <…> Strat_003 <…> (blank) (blank) Sec_003 <…> Project blank Strat_004 <…> (blank) (blank) Sec_004 <…> In cases wher Project is not part of the original relationship, the Project columns in the joined table would be blank.
Is there any reason why this wouldn't work, given how you are using the model for reporting?
Please post back with more detail if needed.
Regards
