Data Modeling, Table Relationships and DAX Basics

Data Modeling

A data model is the set of tables in your report and how they connect. A good model makes reports fast and calculations correct.

Analysts sort tables into two kinds:

• Fact tables record events and hold the numbers: each sale, each order line. They have many rows. Example: Sales (Date, ProductID, CustomerID, Quantity, Amount).
• Dimension tables describe the things involved in those events: Products, Customers, Dates, Stores. They have fewer rows and hold the labels you filter and group by.

Arrange them in a star schema: the fact table in the middle with each dimension connected to it, like the points of a star. If you have one big flat file, you can split it into a fact table and dimension tables in Power Query (reference the query, keep the product columns, remove duplicates).

Always add a Date table. A dedicated calendar table (one row per day, with Year, Quarter, Month columns) lets you analyze by any time period. Create one in Modeling > New Table with Date = CALENDARAUTO() and mark it as a date table.

Table Relationships

A relationship connects a column in one table to a matching column in another, usually a dimension's key (ProductID in Products) to the same column in the fact table (ProductID in Sales). Create it in Model view by dragging one column onto the other.

Each relationship has two settings to check (double-click the line):

• Cardinality: almost always one-to-many (1:*). One product appears once in Products but many times in Sales.
• Cross-filter direction: Single is the safe default. Filters flow from the dimension (the "one" side) to the fact table (the "many" side), so clicking a Category filters the Sales.

How to tell a relationship is wrong: a visual shows the same total on every row (the tables are not connected), or numbers are far too big (the relationship is on the wrong column). Go to Model view and check the lines.

DAX Basics

DAX (Data Analysis Expressions) is Power BI's formula language. It looks like Excel formulas but works on whole columns and tables.

Measures vs calculated columns:

• A calculated column is worked out once per row and stored in the table, like adding a column in Excel. Use it for labels you want to slice by (for example Price Band).
• A measure is calculated on the fly for whatever is filtered in the visual. Use measures for totals, averages and ratios. Most of your DAX should be measures.

Create a measure with Home > New Measure:

Total Sales = SUM(Sales[Amount])
Total Cost = SUM(Sales[Cost])
Profit = [Total Sales] - [Total Cost]
Profit Margin % = DIVIDE([Profit], [Total Sales])
Order Count = COUNTROWS(Sales)
Customers = DISTINCTCOUNT(Sales[CustomerID])
West Sales = CALCULATE([Total Sales], Products[Region] = "West")

• Columns are written Table[Column]; measures are written [Measure].
• DIVIDE is safer than / because it returns blank instead of an error when dividing by zero.
• CALCULATE changes the filter for a calculation. It is the most important DAX function; for now, use it to calculate a value for one specific group.

Format the measures (currency, percentage) in the Measure tools ribbon so they display correctly everywhere.