Data Model Problem

[Elimey68]

Dear all, 
I need your help with a problem I am facing with my data model. 

I have a model as follows: 

the rules are:

one Account can have many Strategies

One Strategy can have many StrategicEntities 

One StrategicEntity can have many Initatives 

One StrategicEntity can have many KPIs

Up until this part, everything is straightforward and easy. 

The part that I can't develop is:

Each Initiative (KPI) can have many parent-child relations in them. It means each Initiative is like a tree, and each node can have different leaves or can be the parent itself. 

How can I develop this? 

the link to the pbix file:
https://drive.google.com/file/d/1l-ax7lIOnHtN9AurLBxW3hC01GSCadGs/view?usp=sharing

[Elimey68]

@speedramps 

"Also the right hand relationship from Account has been truncated ... so we don't know what that is."

the part that is truncated is not a part of this problem.

[v-easonf-msft]

Hi， @Elimey68 

I didn't find the Table ‘KPI’ in the pbix and I‘m not sure what you mean by 'Each Initiative (KPI) can have many parent-child relations in them. It means each Initiative is like a tree, and each node can have different leaves or can be the parent itself. '

Can you describe your needs and expected outcomes in a straightforward way?

In addition, sometimes it should be noted that the relationship does not have to be established, perhaps you can also obtain corresponding results through Dax.

Best Regards,
Community Support Team _ Eason

[speedramps]

We want to help you but your description is too vaugue. Please write it again clearly.

There is no contect or example data or details of what you want to report. 

Also the right hand relationship from Account has been truncated ... so we don't know what that is.

Provide example input data as table text (not a screen print) so we can import the data to build a solution for you.
Also provide the example desired output, with a clear description of the process flow.

Remember not to share private data ... we don't want you to get into trouble. 😧

Take care to use the same table and field names in the input, output and description so we can understand your problem and help you.

You will get a quick response if you put time and effort into writing clear problem descriptions.

Vaugue descriptions can waste your time and ourtime.

Look foward to helping you when the above information is forthcoming

Here are some data modelling tips ...

Don't build you data model exactly like the source data. Learn how to reshape it using Power Query.

Don't daisy chain Accounts to Strategy to StrategyEntity.
Use Power Query to Merge them into one table. 
Whilst that would be bad pratice for a 3rd normal form SQl tablular database.

It is good pratice for a Power BI column database to flatten data like this.

It wont use more disk space in Power Bi, it will use less, because that is how column databases work.

You will then have 1 dimension table with  Accounts / Strategy / StrategyEntity.

And 2 fact tables (initiaitives and KPIs)

Thanks for reaching out for help.

I put in a lot of effort to help you, now please quickly help me by giving kudos.

Remember we are unpaid volunteers and here to coach you with Power BI and DAX skills and techniques, not do the users job for them. So please click the thumbs up and accept as solution button. 

If you give someone a fish then you only give them one meal, but if you teach them how to fish then they can feed themselves and teach others for a lifetime.  I prefer to teach members on this forum techniques rather give full solutions and do their job. You can then adapt the technique for your solution, learn some DAX skills for next time and soon become a Power BI Super User like me. 

One question per ticket please. If you need to extend your request then please raise a new ticket.

You will get a quicker response and each volunteer solver will get the kudos they deserve. Thank you ! 

[Elimey68]

@speedramps 

Thank you for your reply. 
I will try the suggested solution. Meanwhile you can check the example file I attached

[speedramps]

Ok, in that case please see my previous answer and questions.

Either clisk thumbs up and accept the solution or provide the requested info. Thanks