Role Playing Dimensions(don't want to use UseRelationship)

[RG007]

Hi let's talk standard scenario of Date dimension that needs join with fact table having multiple date fields like order date, ship date, delivery date etc.

I don't want to use active/Inactive relationship and then use of UseRelationship.

I want to go for traditional apporach of creating multiple copies of Date dim.

I did create 3 references(right click on original date dim and click on reference) of Date Dim name them Like Order Date, Ship Date and Delivery Date and then joined these three tables with 3 respective fields in Fact table.

Is it right way, if not why? also please advise the best way.

Thanks

[amitchandak]

@RG007 ,Yes, You have create one date dim and three joins with them one active two inactive and use userelation to activate them .

refer userelationship

https://radacad.com/userelationship-or-role-playing-dimension-dealing-with-inactive-relationships-in...

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

[RG007]

if you read my original post, I have written very clearly that I don't want to go that route like using one active and multiple inactive realationship and then use code(userelationship etc) to use inactive relationship.

I want to create multiple alias of original date dimension.

so one way is create duplicate, another is create reference...just trying to figure out what is best way.

Thanks

[v-yiruan-msft]

Hi @RG007 ,

Please review the following blog, you will get more explanations about the two methods you mentioned.

Multiple Relationships Between Tables in DAX

Best Regards

Rena

[RG007]

Hi Rena thanks for your reply, we are going for 2nd option , but my question is since I need multiple copy of date/calendar table , can I use the original calendar table and make another calendar table using REFERENCE.

What is best/most efficient way to make multiple copy of date/Calendar .

Thanks