Forum Discussion

mattmickeyj's avatar
mattmickeyj
Frequent Visitor
6 years ago

Not automatically detecting relationships

Hi,

 

I am very new to PowerBI - I have 2 tables, imported via a query using the advanced editor.  In options > current file > data load > relationships, all the 3 boxes are ticked, however PowerBI Desktop is failing to auto-detect relationships, even when I press autodetect in Manage Relationships.  The column headers are identical e.g. school and _Year

 

 

When I try to manually make more than 1 relationships I can't and I get the error:

 

Any guidance would be really appreciated, thank you 🙂

 

Matt

Need Help

4 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    6 years ago

    Autodetection is a bit of a black box. Power BI considers data types, names of columns, the values that are found in the columns, etc. 

     

    There can only be one set of active relationship paths between 2 tables. It is no problem creating inactive relationships, you just need to use USERELATIONSHIP in your measure. The other thing that you can do is create a composite key by appending multiple columns into a single column in both tables. You can do that in Power Query or DAX.

    • mattmickeyj's avatar
      mattmickeyj
      Frequent Visitor
      6 years ago

      Thanks for that.  As I said I am very new to that - can you provide some steps as to how I would achieve that please?

      • Greg_Deckler's avatar
        Greg_Deckler
        Community Champion
        6 years ago

        mattmickeyj Are you referring to a composite key or USERELATIONSHIP or both?

         

        To concatenate columns in DAX is simply:

         

        [Column1] & [Column2] & [Column 3] ...

         

         

  • v-alq-msft's avatar
    v-alq-msft
    Community Support
    6 years ago

    Hi, mattmickeyj 

     

    Power BI Desktop looks at column names in the tables you're querying to determine if there are any potential relationships. If there are, those relationships are created automatically. If Power BI Desktop can't determine with a high level of confidence there's a match, it doesn't create the relationship.

     

    It is suggested to define active relationships whenever possible. They widen the scope and potential of how your model can be used by report authors, and users working with Q&A. It means that role-playing dimension-type tables should be duplicated in your model. 

     

    In specific circumstances, you may define one or more inactive relationships for a role-playing dimension-type table. You can consider this design when:

    • There's no requirement for report visuals to simultaneously filter by different roles
    • You use the USERELATIONSHIP  DAX function to activate a specific relationship for relevant model calculations

     

    For further information, you may refer the following documents.

    Create and manage relationships in Power BI Desktop 

    Active vs inactive relationship guidance 

     

    Best Regards

    Allan

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.