Relationships

One of the most misunderstood elements in Power BI models is the concept of relationships. Whether you are working with Power BI coming from an Excel background, or you have been educated in relational databases, relationships in Power BI models require an approach that is different from what you are familiar with.

Data in Excel

Let us zoom in on data in Excel first. The concept closest to a database in Excel is that of Excel tables. You could consider an Excel table as a "flat" database. This way of storing data has many disadvantages.

As an example, the figure below shows data as it may be stored in an Excel worksheet:

Figure 1.2.2: A table in Excel

This table contains order amounts and dates for sales orders, which are sold by employees. There are multiple issues with flat databases:

• Clearly, all information about an employee (like job role and date of birth) is duplicated with each order sold by that employee...