Forum Discussion
Helper IDuplicate DateKey Relationship To 1 Table? (Correct Model Configuration?)
I'm attempting to configure my data model, but have a question as to 'best practice' for this type of situation...
I have a table that contains appointments. The appointments have two date properties of intrest:
- Appointment Date = Date of the individuals appointment
- Create Date = Date the appointment record was created
I have a typical DateKey table in my model, but I'm a bit confused about how to setup the relationship in the model. Do I:
- Create a *:1 on appointments.appointment_date = DateKey.Date (cross filter both) & create *:1 on appointments.create_date = DateKey.Date (cross filter both)
- Only create 1 of the two relationships
- Don't create any relationship, and powerBI will utilize the datetable, as it is marked as a date table and no relationship is needed?
Hi pe2950,
your scenario is known as Role Playing Dimensions. You can create an active relationship between the Date dimension and the column from the fact table which you're more likely to use and an inactive relationship between the Date dimension and the other column in the fact table. When you want to browse your model by the date of the inactive relationship use the USERELATIONSHIP function to activate it in your measure
1 Reply
- LivioLanzo
Solution Sage
Hi pe2950,
your scenario is known as Role Playing Dimensions. You can create an active relationship between the Date dimension and the column from the fact table which you're more likely to use and an inactive relationship between the Date dimension and the other column in the fact table. When you want to browse your model by the date of the inactive relationship use the USERELATIONSHIP function to activate it in your measure