Data Modeling
Power BI · Data Modeling
Theory
Data modeling is the step where you decide how tables relate so Power BI can filter and aggregate correctly across them. In Model view each table is a box, and a relationship is a line between a key column in one table and the matching column in another. When a user picks a value in one table, for example City = Jaipur in Customers, the filter travels along the relationship to Sales and every measure shows only Jaipur's orders.
A good model is small, clear and shaped as a star schema: a central fact table of events (Sales) surrounded by dimension tables that describe them (Customers, Products, Date). Good modeling also means correct data types, hiding technical key columns from report authors, sorting month names by month number, and writing measures instead of relying on implicit sums. Most slow or wrong reports trace back to a weak model, not to the visuals.
Why we use it
The model decides whether totals are right and visuals are fast. With clean relationships, one simple measure like SUM(Sales[Amount]) works correctly for any slice: region, city, category or month.
When to use it
Model as soon as you have more than one table, before building visuals. Avoid the shortcut of merging everything into one huge flat table; it duplicates data, grows the file and makes DAX harder.
How it works
- Tables loaded into the model
- Identify fact tables (events) and dimensions (descriptions)
- Create one-to-many relationships from dimensions to facts
- Any many-to-many or bidirectional links? Yes: Rethink the design, add a bridge or dimension. No: Keep single-direction filters.
- Hide keys, set sort orders and data types
- Model ready for measures and visuals
Syntax
Model view → drag Sales[CustomerID] onto Customers[CustomerID]
Or: Home → Manage relationships → New
Properties: Cardinality | Cross filter direction | Make this relationship active
Column properties: Hide in report view | Sort by column | Data categoryExample
Sample model setup:
1. Customers[CustomerID] 1 → * Sales[CustomerID]
2. Products[ProductID] 1 → * Sales[ProductID]
3. 'Date'[Date] 1 → * Sales[OrderDate]; mark 'Date' as the date table
4. Hide Sales[CustomerID], Sales[ProductID] and Sales[OrderDate] (use the dimension columns instead)
5. Measure: Total Sales = SUM(Sales[Amount])
Now Customers[City], Products[Category] and 'Date'[Month] all slice [Total Sales] correctly.Key points
- Star schema (fact + dimension tables) is the recommended pattern
- Relationships are typically one-to-many on a shared key
- Cardinality and cross-filter direction affect how filters propagate
- Hide foreign keys so authors use dimension columns
- Use explicit measures rather than implicit column sums