Forum Discussion
Best Practice with multiple UserIds and multiple Dates
I have multiple tables with multiple userIds and multiple dates to be used. What is the recommended practice for relating tables.
I can't relate 2 fields to the same table because on will become inactive. I can't create MasterDate or MasterUser tables. Additional relationships to those tables become inactive because it will create ambiguity between Users and Tasks or Users and Dates.
Current structure:
TASKS
TaskUserid
TaskStartDate
TaskCompletedDate
TaskAssignedDate
UnitStartDate
UnitCompletedDate
USERS
UserId
ITEMS
ItemCreatedByUserId
ItemUpdatedByUserId
ItemCreatedOn
ItemUpdatedOn
Any help would be appreciated.
Anonymous , Depending on the need you need to have one or more date/user table.
If you want all dates to filter on same month (startdate, completed date) etc then you have create inactive relation and use userelation (same is true for users)
Inactive join can be activated using use relation.
But if you need like start in last month and ended in this month, then you can use the dates or the calendar for each date.
Same is true for user id.
Video how to use userelation: https://www.youtube.com/watch?v=e6Y-l_JtCq4
Anonymous You can only do this with either inactive relationships or multiple User tables and Date tables. The DAX function USERELATIONSHIP works well for many cases and can be more efficient than creating multipl Date tables, but depends on what end result you're after.
5 Replies
- amitchandak
Super User
Anonymous , Depending on the need you need to have one or more date/user table.
If you want all dates to filter on same month (startdate, completed date) etc then you have create inactive relation and use userelation (same is true for users)
Inactive join can be activated using use relation.
But if you need like start in last month and ended in this month, then you can use the dates or the calendar for each date.
Same is true for user id.
Video how to use userelation: https://www.youtube.com/watch?v=e6Y-l_JtCq4
- AllisonKennedy
Community Champion
Anonymous You can only do this with either inactive relationships or multiple User tables and Date tables. The DAX function USERELATIONSHIP works well for many cases and can be more efficient than creating multipl Date tables, but depends on what end result you're after.
- AllisonKennedy
Anonymous If you want to have a slicer for StartDate and a slicer for CompletedDate, you will likely need two date tables, since you want to have different filters on each date. USERELATIONSHIP only really helps with the measures, so stuff that you can put in 'Values' of a visual. If you want to slice or dice (Rows, axis, slicer) then you are probably looking at needing another dimension table.