Star chema with parent-child hierarchy in a dimension

[Anonymous]

Hi everyone,

My post is related to the star schema that I want to use in PowerBI.  I am trying to transpose a relational database into a star schema but I have a problem on one of my tables.

This table is called "Contract", and is composed of contracts and sub-contracts.

A sub-contract is necessarily attached to a contract, by a "parentId" column which is the id of the "parent" contract. A contract can have several sub-contracts, but a sub-contract cannot have a sub-contract (so a maximum depth of 1).

My company marketing service wants to see the revenus per contract, per sub-contract or per contract+sub-contract. 

I would like to know how I should handle this problem : should I keep the table as it is and make it a dimension (so in this dimension I would have the contracts and the sub-contracts, with a parentId key) or should I do it another way ?

Thanks in advance for your help!

[amitchandak]

@Anonymous , refer if these two can help you

https://radacad.com/parsing-organizational-hierarchy-or-chart-of-accounts-in-power-bi-with-parent-child-functions-in-dax

https://www.thebiccountant.com/2019/10/03/parent-child-hierarchies-with-multiple-parents-in-power-bi-with-power-query/