In Power BI, reports with incorrect figures are often not due to DAX formula errors. The underlying cause is more fundamental: the table containing the numbers and the table explaining the numbers are mixed into one table. Separating them and then identifying which is which is the first step in building a tidy data model.
Two types of tables, two different tasks
Microsoft's star schema guide explains the division. Fact tables store observations or events, such as sales orders, stock balances, or exchange rates. They contain key columns that point to dimension tables, plus numeric columns that will be summed or averaged. The number of rows can be large and continuously increasing.
Dimension tables describe the "objects" being analyzed: products, customers, stores, dates. They contain one unique key column, plus other columns for filtering and grouping. The number of rows is relatively small.
| Fact Table | Dimension Table | |
|---|---|---|
| Answers | How many, what value | Who, what, where, when |
| Typical Content | Key + numbers (quantity, revenue, profit) | Unique key + attributes (name, category, region) |
| Growth | Continuously increases with each transaction | Changes slowly |
| Relationship Side | "Many" side | "One" side |
How to determine without guessing
In Power BI, the type of table is not set through a single property. According to Microsoft documentation, the type is determined by the relationships in the model: in a one-to-many relationship, the "one" side is always the dimension table and the "many" side is always the fact table. So, you can check it with three questions:
- Do new rows appear every time there is an event? If yes, it is most likely a fact table.
- Is there one column with unique values in each row? If yes and that table describes an entity, it is a dimension.
- Is the numeric column suitable for summation? Columns like unit price should not be summed, so create an appropriate measure and hide the raw column.
Determine grain before anything else
The Kimball Group refers to grain, which represents what one row of a fact table signifies, as a crucial step in dimensional design. Grain must be established before selecting dimensions or facts. For example, "one row per item in one order" is a clear grain statement. The following rule is equally important: different grains should not be mixed in one fact table. Sales targets per month per product, for instance, should not be crammed into a daily transaction table. Separate them into their own fact table, then connect both through the same dimension.
Types of numbers in fact tables
Not all numbers can be summed indiscriminately. Kimball distinguishes three types:
- Additive: can be summed across any dimension, such as the number of units sold.
- Semi-additive: can be summed across only some dimensions. Balances, for example, can be summed across accounts but not across time.
- Non-additive: cannot be summed at all, such as ratios. Store the additive components, sum them, then calculate the ratio.
Common mistakes
Storing product names and categories directly in the sales table creates repetitive data in each row and complicates filtering. Microsoft recommends that fact and dimension tables not be mixed into one table, with the exception of degenerate dimensions like invoice numbers. Date dimensions should also be created separately, as they are the most consistently used dimension in almost all models.
If you prefer to see the explanation directly, there is a short clip on this topic at taalenta.id/video/pbi-apa-bedanya-fact-table-dan-dimension-table-di-power-bi.
Sources
- Microsoft Learn, Understand star schema and the importance for Power BI
- Microsoft Learn, Describe dimensional schema types
- Kimball Group, Grain
- Kimball Group, Additive, Semi-Additive, and Non-Additive Facts