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 🙂

JoaoMarcelino's avatar
JoaoMarcelino
Responsive Resident
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

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's avatar
    JoaoMarcelino
    Responsive Resident
    2 years ago

    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'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