In my operational database, the tables are structured in such a way that each object has its own ID and the relationship between objects are stored in another tabes called as relation tabe.
Essentially, the relation table contained a parent child relationship. I added an extra column in it called as PATH, that I can use to traverse through the relations.
So the tables are City, State, Country and Continent.
Most Important is this table which shows the path
Question : What is the best way to model this kind of structure in Power BI?
The below obviously doesnt work the best.
Shall I make a bridge table and then link the Table_Rel to each dimension? Has anyone come across such modelling ?