Not automatically detecting relationships

[mattmickeyj]

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

[v-alq-msft]

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.

[Greg_Deckler]

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]

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]

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

To concatenate columns in DAX is simply:

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