Forum Discussion

PWVL_Arno's avatar
PWVL_Arno
Frequent Visitor
2 years ago
Solved

Multiple relations between tables: duplicate or reference

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...
Need Help
  • JoaoMarcelino's avatar
    JoaoMarcelino
    2 years ago

    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's avatar
PWVL_Arno
Frequent Visitor
2 years ago

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:

RequestAffected PersonReporting PersonResponsible Person
Req 1Person APerson BPerson C
Req 2Person XPerson XPerson 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's avatar
JoaoMarcelino
Responsive Resident
2 years ago

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