Power BI Journey: Blog #3

I am now in the third tutorial on Power BI. In this lesson, the topic is on creating and managing relationships. Important concepts that were introduced are cardinality i.e. many-to-1 (*-1) or (one-to-many (1-*), cross-filter where if we select "Both", this means that we can think of many separate tables as one table.

I have these three separate tables.

Apocalypse Sales:

Apocalypse Store:

Customer_Information:

Notice that each of the values in the Customer ID column of the third table can be seen in the Cust ID column of the first table. But notice that there are only four rows in Customer_Information table while there are many rows in the Apocalypse Sales table. The cardinality of the relationship means having unique or multiple instances per value for the joining field between two tables. This means that the cardinality of the relationship from Apocalypse Sales and Customer_information in terms of Customer ID = Cust ID is Many-to-one. Conversely, the cardinality of the relationship from Customer_information to Apocalypse Sales in terms of Customer ID = Cust ID is one-to-many. If we relate this to SQL, Customer ID is the Primary Key.

Using the same principle, the cardinality of the relationship between Apocalypse Store and Apocalypse Sales in terms of Product_ID is one-to-many. Conversely, the cardinality of the relationship between Apocalypse Sales and Apocalypse Store in terms of Product_ID is many-to-one.

This is the reason why we have the following model.

As an output, I was able to create a table that list the different states associated with each customer and then determine the number of product ids each customer purchased.

In other words, in the Apocalypse Store, we have 10 products being sold and the customer from Minnesota state has purchased 7 out of the 10 products.