What is the best way to handle refences to multiple people?

[handler1976]

Trying to learn modeling in the star schema and came across this situation that I need help understanding.  Let say I have a table called TICKETS that has columns: ticketid, category, createdby, solvedby and another tabled called PERSON with a column called employee.  There is an active relationship from employee to createdby and inactive relationship to solvedby.  I create a bar chart that contains the count of tickets and a slicer for person.  The slicer is only shows me the count for the active relationship, so if Bob has 2 tickets created and Joe solved both those tickets, I want to use the filter to show me the number of tickets solved or created.  Do I create separate dimensions for each type of person, so a CreatedBy and SolvedBy dimension?  

Thanks

[DataNinja777 · Accepted solution]

Hi @handler1976 ,

In your scenario, the best way to handle references to multiple people in a star schema is by creating separate role-playing dimensions for each role, such as CreatedBy and SolvedBy. This approach simplifies modeling and avoids issues with inactive relationships and USERELATIONSHIP. You can achieve this by duplicating the Person table into two new tables: Person_CreatedBy and Person_SolvedBy. Then, you establish two active relationships from the Tickets table: one from Tickets[createdBy] to Person_CreatedBy[Employee], and another from Tickets[solvedBy] to Person_SolvedBy[Employee].

If you prefer to keep a single Person table, you would have to rely on DAX using USERELATIONSHIP to activate the inactive relationship when needed. For example, to count tickets solved by a person:

Tickets Solved Count = 
CALCULATE(
    COUNTROWS(Tickets),
    USERELATIONSHIP(Tickets[solvedBy], Person[Employee])
)

However, using this method, the slicer only applies to the active relationship (e.g., createdBy), so if you want to filter based on who solved the ticket, you'd need separate measures and can't use the slicer effectively for both roles.

To show the number of tickets either created or solved by a person, you can use a custom measure like:

Tickets Created or Solved Count =
CALCULATE(
    COUNTROWS(Tickets),
    FILTER(
        Tickets,
        Tickets[createdBy] = SELECTEDVALUE(Person[Employee]) ||
        Tickets[solvedBy] = SELECTEDVALUE(Person[Employee])
    )
)

This formula allows you to reflect both roles in one measure, but again, it works only for specific visuals and not for slicer filtering. Therefore, the most robust and scalable solution is to create separate dimension tables for each role.

Best regards,

[timalbers]

Hi @handler1976 

if you want to have real dimension filters in your report you probably should clone the dimension table, so that you are having each a creator dimension and a solver dimension.

Another cool approach without having duplicate dimension data would be creating two different measures for both situations:

Tickets Created = COUNTROWS( Tickets )

Tickets Solved =
CALCULATE(
    COUNTROWS( Tickets ),
    USERELATIONSHIP( Person[employee], Tickets[solvedby] )
)

You could use them in a table or matrix visual to show side by side both the amount of created and also solved tickets for each employee.

[handler1976]

Thanks.  Do you see times when you would use both?  For example, if you wanted to filter for tickets created by Person 1 and Solved by Person 2, I assume I would have to have separate dimension tables.  

[timalbers]

Your assumption is correct.

I'd say it's an either/or solution. If you already created two different dimension tables, then there's no need to create a second measure with USERELATIONSHIP - as there's no inactive relationship. In this case I'd only create a single measure:

Ticktes Count = COUNTROWS( Tickets )

[MFelix]

Hi @handler1976 ,

For this case I suggest a couple of adjustments to your realtionships and also the use of two different metrics:

• Change your relationships from one to one with bi directionla filter to one to many
  • You should have the one side of the relationship on the Persons table and the Many side on the tickets tables
  • Turn do the filter has single select
  • Keep the active and inactive relationship has you have
• Create the following measures

Cresated Tickets = COUNTROWS(Tickets)

Resolved Tickets = CALCULATE( [Cresated Tickets], USERELATIONSHIP(Person[Employee],Tickets[SolvedBy]))

This should give you the expected result, I'm assuming that you have only one ticket line on the Tickets table and that the ative relationship is on the Createdby column

[rajendraongole1]

Hi @handler1976  -you can use same Person table for both roles, then for any measure that depends on the solvedby relationship, you must explicitly activate the inactive relationship using USERELATIONSHIP.

measure:

Eg: TicketsSolved =
CALCULATE(
COUNTROWS(Tickets),
USERELATIONSHIP(Tickets[solvedby], Person[employee])
)

Use the same slicer from the person table and create tickets created measures too . Hope this helps.