Mmultiple id of the same relation

[AndrewTFox]

Hello,
I'm trying to process some mysql data but I'm stuck with a transcoding problem.
I try to give a simple example and explain where my difficulty is:

"tblUsers" table, "Username", "idUser" fields.
"tblTicket" table, "idAuthor", "idAssignedto", "Ticket" fields.

Both "idAuthor" and "idAssignedTo" refer to "idUser" of the "tblUsers" table, so I can create only one relationship, but if I wanted to create a table in which I see "idAuthor", "idAssignedTo" and "Ticket", it would only return the name of the related field .

Some solutions I have thought of are:
- Substitute Values: I fill in the values by hand (absurd)
- I create a copy of "tblUsers" and create another relationship with the missing field.

However, I believe that there are more logical solutions, but I don't know of their existence. Can someone help me?

I hope I have been able to explain myself sufficiently.

This is what I have:

 

This is what I would like:

Thanks.

NB.
What I have done is just an example, but in all other tables I have the UserID instead of the username.

[OwenAuger]

That will most likely work - essentially flattening dimensions into the fact table.

However I would say it's still best practice to follow the star schema approach and keep dimension tables separate.

If you are doing anything "complex" in DAX involving either of those dimensions, you may encounter some quirks later on and may need to revert to dimension tables.

Also, you may see some benefits in terms of performance or model size with separate dimensions, e.g. if you are including those columns on slicers (depending on the cardinality of those columns).

But if you are simply displaying the values of those columns in a visual, it should at least work.

Regards,

Owen

[v-stephen-msft]

Hi @AndrewTFox ,

Your problem is that in the table visual, both idAuthor and idAssignedto are showing numbers instead of text, right?

First, check to see if the number appears due to the wrong selection of the aggregation operation. We need to choose 'Don't summarize' here.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[OwenAuger · Accepted solution]

Hi there,

What you've described here is an example of a role-playing dimension. In your data modeo, tblUsers plays the role of both Author and AssignedTo.

The Microsoft docs on this cover the two main options you have:

1. Create one active relationship and one or more inactive relationships between fact table and dimension table. The inactive relationship(s) can be activated using the DAX function USERELATIONSHIP in particular calculations.
2. Create multiple copies of the dimension table (which you mentioned in you post), and relate each to the appropriate foreign key in your fact table.

Since you need to include attributes from both Author and AssignedTo in a single visual, you really have to go with option 2.

It is best to ensure that the columns of each copy of tblUsers are self-describing, e.g. "Author Username" and "AssignedTo Username".

There are various other articles/posts online covering this but this is the basic idea.

Regards,

Owen

[AndrewTFox]

I propose another solution that I have been thinking about these days:
Make 2 query merge with the tblUtenti expanding the username fields.

Do you think it is a valid solution?

Results: