Power BI Tutorial: Many to Many Relationship using Power BI Designer

Quick disclaimer, the Power BI Designer is in preview right now and as such it is lacking a number of very important features that some consider minimally necessary for the product to be usable (a few thoughts on where the product is in its current state from my perspective could be found here). At the same time, Power BI Designer is clearly one of the most promising new tools that came out under the Power BI umbrella and therefore I decided to post a quick Lab highlighting one of the new exciting features for the product – ability to create Many-To-Many Relationships.

I will not be formally defining what Many-To-Many Relationships are, but even a very quick web search will provide plenty information on this topic.

Also, this lab will not be as detailed as the ones I posted before because the Power BI Designer is still rapidly evolving; therefore, I will only highlight the salient points for now and will add the depth and individual steps when the product is a little bit more evolved.

Now let’s take a look at the problem we are trying to solve. Let’s say I work in a Marketing department and I am trying to classify different manufacturers who compete in my company’s space. One of the ways I might classify them is by size and another, by them being an actual threat to my company (so if my company is Tesla, it is interesting to look at the auto market overall by size, but only those manufacturers that produce electric cars are actually a threat to my business). I may classify these manufacturers like the table below:

We can see that all of the manufacturers have been placed in a certain Size category and two of them have been identified as Threat.

You can download the Designer model here. And you can download the Power Designer Preview here. The model will contain all the data already loaded, the future revisions of this lab will include the source data and the necessary PQ transformation instructions.

At a very high level, the model will look like this

And from the end-user perspective, this is what is available for analysis:

A new feature in the Power BI Designer allows us to create a new type of relationship between ManufacturerClassificationBridge and Classification (and subsequently, Manufacturer tables)

This allows us to apply an automatic filter on Manufacturers when a Classification is being filtered as well. So if I were to filter a specific classification, like Threat, the model will then find corresponding records in the ManufacturerClassificationBridge table and then filter out manufacturers in the Manufacturer table that are not part of the Threat classification.

I know that this can probably be a little confusing, therefore, let’s look at the visualization behavior to try to make sense of it.

In our case I created a chart that starts our analysis at a state level (in our data set there is only one state PA) and if we mouse over it we will see that the total sales amount is $105,009.45

If we look at the Axis for the chart, we will see that we will be drilling from State->Classification->Value->Manufacturer. What is interesting is that typically, when we drill into a total, the resulting data will sum up to that total, but in our case it should not because a manufacturer could be part of two different Classifications therefore we should double count those sales. If you double click on the PA you will indeed see that the sum of sales by different classifications is in fact larger than the total sum, in other words, for each classification the number is correct, but if you add them together, the number will not total PA number of $105,009.45

We can see that since all of our manufacturers have been classified by sum, the Size classification amount is the same as the PA amount, but we only have two manufacturers in the Threat classification, therefore we see only $24, 312.30 there.

I understand that this may not explain everything in great details but many customers asked me to post something that they can play with as they try to understand how Many To Many relationships work, so I will post it as is for now, but I promise to come back and add more content to this lab soon.