How Would you Create dimention Model In this Case

[GAPER]

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.

[OwenAuger]

Hi @GAPER 

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