Forum Discussion
Data Modeling: Dimension Table with Component from Another Dimension
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?
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/
4 Replies
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-power-bi
Can you post an image of your sample model or post the PBIX?
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.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/
