Replace Index with Values

[Anonymous]

Hello guys, 

I'd need a hand with the model below. Table 1 shows uniques titles (movies, series, etc.) with its corresponding index "tconst". Table 2, shows uniques Names with its index "nconst". Table 3 shows every title (tconst), a person (nconst) and its role in the title.

Table 4 shows every title (tconst) and its director and writer (both are indexes nconst).

Which would be the best way to make a visual with: a title -> Name (from table 2) of Director -> Name (from table 2) of Writer?

Thanks!!

[lbendlin · Accepted solution]

That's quite a handful.

This part needs cleaning up.  Combine Location,Country and Region into one table, and clean up your "region"  terminology.  Your title region seems to be different from your geographic region.

Bidirectional 1:1 relationships most of the time mean you should fold the tables into a single table.

I think you have more than two fact tables. In fact (pun intended) you may not have fact tables at all.  This gets close to a graph,  and frankly is not something Power BI is good at.  The Qlik associative model is more suited for such a scenario.

Can you try to formulate the main questions that your report should be answering for your intended audience?  That may help to modify the data model in a meaningful way.

[Anonymous]

Many thanks for the help and advice @lbendlin . I modify the questions in order to ease the help of the audience.

[lbendlin]

Please post a sample pbix if possible.

[Anonymous]

You can take a look here:

https://1drv.ms/u/s!Aty3aFli-QGzhDtiMsQRl-vjaqoj?e=Rode0J

thx!

[lbendlin]

in a "normal" data model the filter direction is singular, from the 1 table ("dimension") to the * table ("fact").

Can you explain why you chose bidirectional?

[Anonymous]

If filter direction is singular, visualizatons are showing totals and not individual quantities for each dimension. See examplen below on genre visual: