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

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 🙂

6 Replies

  • 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

  • JoaoMarcelino's avatar
    JoaoMarcelino
    Responsive Resident
    2 years ago

    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