Creating relationships between datasets can be a powerful tool in data analysis. It can help you in cross-referencing the data (based on selected field(s)) and finding useful insights without having to merge them into one dataset - potentially risking creation of a 'monstrous' file.

Regardless of the type of relationship, most data tools allow you to look into ‘linking’ two datasets based on more than one parameter. This can be an extremely useful feature if you happen to have data with granularity identifiable by more than one column. For example, imagine you have a company which stores its Sales and Target data in separate files. One day, your boss comes up to you and asks you to look into analyzing both of the datasets together, for all of your products.

In this particular scenario each product reappears each year, meaning that linking appropriate SALES and TARGET fields needs to be defined by both ITEM and DATE.

One way to approach this task would be just by combining the tables together by a join. This, as I mentioned earlier isn’t always an option as sometimes it may lead to your data ‘exploding’ in size. Normally, you could ask your software to ‘link’ those two tables based on their granularity, which here, as I already mentioned, would refer to both DATE and ITEM field.

Unfortunately, PowerBI does not allow for creating such relationships, limiting its users to creating relationships using only one of the fields. So what can you do? The easiest workaround is simply to create a unique identifier field in both of the tables, which limits the granularity to a single field!

This way when loading the data in the model view, you can be sure that the relationship you create, will definitely match the fields you want to match, without creating a slippery slope of ‘many to many’ relationship. Just remember to always check in the ‘edit relationship’ view that the correct field is selected as PowerBI may sometimes try to match the datasets looking a field which doesn’t define the data granularity!