Multiple relations between tables: duplicate or reference

[PWVL_Arno]

Hi,

I'm making a datamodel and need to have multiple relations between two tables:

The central table is 'Requests'. Each request has a 'ResponsibleUserID', 'AffectedPersonID', 'ReportingPersonID'. All of these colums contain the ID's of persons, and these ID's reference to the "PersonsAndUsers" table. How do I make the data model so I can get data from all three person/user ID's of the Request-table.

I'd prefer to not have to use 'userelationship' in DAX. So I guess I need to have 3 "PersonAndUsers" tables, so I can make a relationship with each of these tables. I duplicated the table in Power Query, but when refreshing the data, it will import the 'PersonAndUsers' data three times? How can I avoid this? Do I need to Reference in PowerQuery instead of Duplicating?

Also, each of these persons has a "DepartmentID", so the same applies to this? I will need the "Department" table three times, so each "PersonAndUsers" table can have a relation with another "Department" table? So I make thee tables: 'ResponsibleUserDepartment', 'AffectedPersonDepartment' and 'ReportingPersonDepartment'?

Thanks a lot!

[JoaoMarcelino]

Hi @PWVL_Arno 🙂

If you think this solution might help other fellow Power BI users, pleasure consider marking it as a solution for better indexation and visibility 🙂

Hope I was of assistance!
Cheers
Joao Marcelino

[JoaoMarcelino · Accepted solution]

Hi @PWVL_Arno 🙂

I was checking your question and I might not have all the information and requirements for your need.
But one tradicional approach is to unpivot, e.g, 1 row will be converted in has many rows and column ID

I've created example tables that you can use also for testing. Code:
Requests - 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUQooyswrSS1SyMsvUSjPL8rOzEsHioJkjMB0rE40mOWcn1tQClKYWaxQnJNfDlego2QMVmQMZLmk5iRWKmTmKRQkViZnpCZng2UhKk2UYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Request ID" = _t, Request = _t, ResponsibleUserID = _t, AffectedPersonID = _t, ReportingPersonID = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Request ID", Int64.Type}, {"Request", type text}, {"ResponsibleUserID", Int64.Type}, {"AffectedPersonID", Int64.Type}, {"ReportingPersonID", Int64.Type}})
in
    #"Changed Type"

PersonsAndUsers-

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUQrOSCzKz1OK1YlWMgJyQ/JzwWxjINsrPwMiYQLk+GYmZySm5ijFxgIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"PersonAndUsers ID" = _t, PersonAndUsers = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"PersonAndUsers ID", Int64.Type}, {"PersonAndUsers", type text}})
in
    #"Changed Type"

The way I would deal with that business need would be to select the 3 ID columns and unpivot the them, resulting in:

This means that you can now create an "healthy" relationship between dimensions table and facts table (one to many):

And now you can filter, make calculations... on the request(s) by person


Hope I was of assistance!
Cheers
Joao Marcelino
Ps- Did I answer your question? Mark my post as a solution! Kudos are also appreciated 🙂

[PWVL_Arno]

Right now I duplicated the personstable for each type of person. So there are different relations between the Requests table and each of the Persons tables. But as I said in my original post, I think this means that the Persons table will be imported three times, which seems wrong. Maybe this approach can be a solution, thank you!

[JoaoMarcelino]

@PWVL_Arno I understand your concern 🙂
And yes, duplicating the tables works for a couple of scenarios, but it is not scalable nor a "good data modelling practice": duplicating tables is, in 99,999999999999% of the cases, the dark side of the force :)))

If you allow me, for the majority of cases, unpivoting in this cases would be the one of the best data modelling solutions, in which you can have better vertipaq compression, optimized dictionaries, better to use dax measures, to have a reliable Users table that allows one source of truth and one filter origin, dispite of the context... and let's not forget, easier for end users 🙂

Now honestly, whichever solution works for you will be the right fit, but maybe this stays for posterity and helps other fellow Power BI developers and users in the future 😉

Hope I was of assistance!
Cheers
Joao Marcelino
Ps- Did I answer your question? Mark my post as a solution! Kudos are also appreciated 🙂
Find me on linkedin

[PWVL_Arno]

Hi Joao,

One more question to dig deeper in the unpivoting solution: won't this solution pose difficulties with creating visuals? Say I want to create this table:

Request	Affected Person	Reporting Person	Responsible Person
Req 1	Person A	Person B	Person C
Req 2	Person X	Person X	Person Z

With the current solution I can do this easily as each column in my visual is a separate column (from the 'Request' table itself or from one of the 3 related 'Person' tabels).

Is it also possible to make this with your solution and how?

Thank you so much for you insight 🙂

[JoaoMarcelino]

Hi again @PWVL_Arno 🙂

Well, I would probably map it like this if besides the names I want the analytics behind it

Besides who, I would map how many 🙂

Or something like this (a kit less pretty in my honest opinion):

For this, again, I've unpivoted the columns affected person, reporting person and responsible person:

I also created the dimension Person:

I connected the tables on a one to many relationship:

Selected a matrix and dragged and dropped the fields, something like this:

Please note that the Selected person is just a simple count of rows of table Requests... which really simplifies DAX and makes the model much lighter and scalable.

We could make the table even more similar to yours with a bit of time and DAX manipulation 😉

Hope I was of assistance!
Cheers
Joao Marcelino
Ps- Did I answer your question? Mark my post as a solution! Kudos are also appreciated 🙂
Find me on linkedin