Power BI's model view relationships can be viewed in terms of joins:
A relationship is computing a join in the background, with the join type including the tables with "Many" (e.g. if the relationship is many-to-one, this is computed as a left join). This can be changed to a full join in the report view manually by enabling "Show items with no data".
A limitation is that only one join condition is allowed! This is fine if you have unique ID fields, but sometimes you have data for which this is not the case.
The following two datasets have book sales information, with one of the tables being broken down by Market, and the other being a summarisation over all of the markets.
We would like to relate the tables together on [Date], [Dimension], and [Dimension 2] to be able to access the summarised sales alongside the individual Market sales.
If you try to drag each field in the model view to create multiple relationships, Power BI creates separate inactive relationships, not one relationship with multiple conditions.
The inactive relationships are not being used, and so the data output is not what we want.
To work around this, we will need to create one field which uniquely identifies each [Date], [Dimension], and [Dimension 2] so we can relate on this single ID field:
Creating an ID field by concatenating in Power Query
To create an ID field, first navigate to Power Query by clicking Transform Data in the Home tab.
Here, we want to convert every non-string join field into a string type so that we are able to concatenate.
Then, create a custom column which concatenates all of the fields we need to create a unique identifier by using the & function.
After, convert all of the data fields back to their original types (in our case, convert [Date] back to a date). Repeat this for both tables. Make sure to have the ordering of the concatenating identical in both tables - the ID strings need to match exactly in both tables!
In my "Total Sales" table, this new ID field is unique, and it matches to ID fields in the "Sales per Market" table. Now I can save and close Power Query, and create a relationship on the ID field.