Forum Discussion

Nemza's avatar
Nemza
Frequent Visitor
6 years ago
Solved

Trouble with relationships on simple data model

Hi there,    I'm struggling with a Power BI concept in the way that relationships are handled.  I come from a SQL background, so this data model seems pretty straight forward to me, however it bre...
Need Help
  • mhossain's avatar
    mhossain
    6 years ago

    Nemza 

     

    Creating bridging table automatically:

     

    1. Go to 'Modeling' tab in your pbi and click 'New Table'

     

    2. write dax, I am assuming, your all tables names like, Table1, table2, Table3 and so on........ and ID field name is 'id'

     

    UniqueAllids =
    DISTINCT(

    UNION(

    SELECTCOLUMNS(Table1,"id",Table1[id]),
    SELECTCOLUMNS(Table2,"id",Table2[id]),
    SELECTCOLUMNS(Table3,"id",Table3[id])
    ))

     

    3. Now you have all the unique IDs from all the tables, and try the relationship to all the tables, on the visual, drag fields from this table, so you can bring more fields to this table, above dax is just an example of one field, you can add more fields to this.

     

    Let me know if you face any challenges.

     

     

     

     

     

mhossain's avatar
mhossain
Icon for Solution Sage rankSolution Sage
6 years ago

Nemza 

 

From the relationship diagram you can see the arrow direction and see it is obvious between User and Project tables, for dax it will be hard to identify the relationship. For the simplicity can you do the below:

 

1). Create a mapping/new table with all the Unique IDs(this table can be automatic), and then create relationship from this table to all the tables. And it should work.

 

 

2). I think end objective will be to create some calculation/measures and drag on the visuals, right? So when you create the measures, create virtual relationship like use "TREATAS"

 

Please let me know if above makes sense.

  • Nemza's avatar
    Nemza
    Frequent Visitor
    6 years ago

    Thanks very much mhossain !

     

    I think option 1) will work well for me. 

    How do I go about creating a bridging table automatically?

     

    • mhossain's avatar
      mhossain
      Icon for Solution Sage rankSolution Sage
      6 years ago

      Nemza 

       

      Creating bridging table automatically:

       

      1. Go to 'Modeling' tab in your pbi and click 'New Table'

       

      2. write dax, I am assuming, your all tables names like, Table1, table2, Table3 and so on........ and ID field name is 'id'

       

      UniqueAllids =
      DISTINCT(

      UNION(

      SELECTCOLUMNS(Table1,"id",Table1[id]),
      SELECTCOLUMNS(Table2,"id",Table2[id]),
      SELECTCOLUMNS(Table3,"id",Table3[id])
      ))

       

      3. Now you have all the unique IDs from all the tables, and try the relationship to all the tables, on the visual, drag fields from this table, so you can bring more fields to this table, above dax is just an example of one field, you can add more fields to this.

       

      Let me know if you face any challenges.

       

       

       

       

       

      • Nemza's avatar
        Nemza
        Frequent Visitor
        6 years ago

        Thank you! Very helpful