What is Data Modeling in Power BI

What is Data Modeling?

Data modeling is the process of structuring and organizing data to optimize performance which involves in various processes, like:

Data connection and transformation from various sources
Defining relationships between tables. Optimizing tables and columns for analysis
Creating calculated columns, measures, and hierarchies

Creating Relationships Between Tables

In Power BI, the tables and its relationship are similar to the working of relational databases. The Relationships define the interaction of tables with each other in reports and visualizations.

Steps to Create Relationships in Power BI

Power BI automatically detects relationships based on column names and data types. To manually create relationships,

Go to Model View
Drag and drop fields to define relationships
Set relationship properties, like cardinality, cross-filtering
Using the Manage Relationships Dialog: Navigate to Modeling → Manage Relationships
Click “New” and select the related columns

Understanding Cardinality and Cross-filtering

Cardinality defines the type of relationship between tables:

One-to-Many (1:*): one table (e.g., Customers) connects to multiple records in another table (e.g., Orders).
Many-to-One (*:1): the primary key in one table links to multiple values in another.
Many-to-Many (:): Used when both tables contain non-unique values and require intermediary logic.
One-to-One (1:1): when both tables have unique records.

Cross-filtering determines how filters apply across related tables:

Single-directional: Filters data from one table to another.
Bi-directional: Allows filtering in both directions.

Working with Date Tables

Date tables are essential for time-based analysis like, year-over-year sales, monthly trends. Power BI also provides a built-in Auto Date/Time feature. Creating a custom date table gives more flexibility.

Creating a Custom Date Table:

Using DAX:


DateTable = ADDCOLUMNS ( CALENDAR (DATE(2020, 1, 1), DATE(2030, 12, 31)),
 "Year",  YEAR([Date]), "Month", 
FORMAT([Date], "MMM"), "Month Number", MONTH([Date]),
 "Weekday",  FORMAT([Date], "dddd") )

Marking as Date Table:

Select the table in Modeling
Click Mark as Date Table
Choose the primary date column

Request more information

What is Data Modeling in Power BI