Data modeling & DAX

A Power BI model is not a spreadsheet. Once your data lives in several related tables, visuals get faster, filters behave predictably and formulas become short. That structure is the star schema.

The star schema

Put the numbers you measure in a fact table (Sales: date, product code, customer id, qty, amount) and the things you slice by in dimension tables (Date, Product, Customer, Store). Each dimension connects to the fact table with a one-to-many relationship on a key column.

Table	Type	Typical columns
Sales	Fact	OrderDate, ProductCode, CustomerID, Qty, Amount
Product	Dimension	ProductCode, Name, Category, HSN
Customer	Dimension	CustomerID, Name, City, State
Date	Dimension	Date, Month, Quarter, FinancialYear

One wide flat table also "works", but repeats every customer's city on every row, bloats the file and makes a clean month-on-month comparison harder.

Relationships

In Model view, drag Product[ProductCode] onto Sales[ProductCode]. The line shows a 1 on the dimension side and * on the fact side, with an arrow for the filter direction. Filters flow from the one side to the many side, which is why selecting a category filters Sales, but selecting a sale does not filter the Product list.

Keep relationships single-directional by default and make sure every dimension key is unique; duplicates block the relationship entirely.

Measures, not calculated columns

A calculated column is computed row by row and stored in the file. A measure is computed at query time, for whatever selection the user has made. Aggregations belong in measures.

Total Sales = SUM(Sales[Amount])
Total Qty = SUM(Sales[Qty])
Sales LY = CALCULATE([Total Sales], SAMEPERIODLASTYEAR('Date'[Date]))
YoY % = DIVIDE([Total Sales] - [Sales LY], [Sales LY])

Use DIVIDE instead of the / operator: it returns blank rather than an error when the denominator is zero, which happens constantly for new stores or new products.

Useful starter patterns

Distinct Customers = DISTINCTCOUNT(Sales[CustomerID])
Sales Delhi = CALCULATE([Total Sales], Customer[City] = "Delhi")
% of Total = DIVIDE([Total Sales], CALCULATE([Total Sales], ALL(Customer)))
Avg Basket = DIVIDE([Total Sales], DISTINCTCOUNT(Sales[InvoiceNo]))

A row that needs the value on that row, such as line value = qty times rate, is the one good case for a calculated column, and even then it is usually better fixed in Power Query.

The date table

Mark a proper Date table (Table tools, Mark as date table) covering every date continuously, including an Indian financial year column running April to March. Time-intelligence functions like SAMEPERIODLASTYEAR will not work correctly without one.

Key takeaways

• Star schema: one fact table, several dimension tables, single-direction relationships.
• Measures for anything you aggregate; calculated columns rarely.
• DIVIDE protects you from divide-by-zero.
• No real date table means no reliable YTD or year-on-year numbers.