Data Modeling: Dimension Table with Component from Another Dimension

[kbol]

I'm in the process of trying to transform a semi-relational data model into a star schema for PowerBI and have been stumped by a situation where one dimension is a component of another.

The two dimensions conceptually are Staff and Date. The problem is that Staff have different roles, which is a criteria we want to slice on. A staffperson can have multiple roles at any given time and those roles can also change with time. Consequently, it is insufficient to simply have two unrelated dimensions because if a staffperson has ever had more than one role, they'll get counted for both in all facts. 

What I tried to do was create a table that has one row for each role a staffperson had on a given day to act as the Staff dimension, related to the fact table on the Staff field. Then I created a relationship from the Date dimension to that Staff table (admittedly breaking the star schema idea) and attempted to add a second relationship from the Date dimension to the fact table. Unfortunately, PowerBI views this as an ambiguous relationship. The behavior I am wanting is that a given Date selection filters both the Staff rows to the roles they had on any given day and filters the fact table to facts occuring within that selection.

I've cerated a toy test report that appears to work using this approach, but unfortunately it will require a bunch of USERELATIONSHIP measures which is what I am seeking to avoid. How can I model this situation more correctly?

[Greg_Deckler]

Can you post an image of your sample model or post the PBIX?

[kbol]

Duplicating the Date dimension does allow another relationship, but that would require two selections to be made when slicing by date. From the user experience side, I need for a single selection on the Date dimension to have the effect of filtering both.

Here is an image of the sample model. I can share the toy pbix, too, but I cannot find a way to attach files here.

[v-chuncz-msft · Accepted solution]

@kbol 

You may take a look at the links below.

• https://community.powerbi.com/t5/Desktop/composite-primary-key/td-p/307604
• https://www.sqlbi.com/articles/physical-and-virtual-relationships-in-dax/

[amitchandak]

Two avoid that you might have to create multiple copies of the table.

Table1 = Table //referenece

Table1 =distinct(Table)//New copy .

Refer this, it discusses role-playing dimension : https://radacad.com/userelationship-or-role-playing-dimension-dealing-with-inactive-relationships-in...