14. DAX Measures

Goal: Write your own formulas for sales numbers. Use DAX.

You can let Power BI add numbers for you (implicit measures). But the better way is to write the formula yourself. A written formula is an explicit measure.

Why write measures? These reasons:

1. You can use the same formula on many visuals.
2. The visual names stay short. You do not rename the values each time.
3. All formulas live in one table. You can find them fast.

Step 1: Make a table for the measures

Measures can sit in any table. But that makes them hard to find. Make one empty table for all measures.

1. Click Home > Enter Data.
2. The window shows one empty column (Column 1).
3. Rename the table: Key Measures.
4. Click OK.
5. Click Close & Apply in Power Query.

Now you have a table named Key Measures. The table has no data. It only holds formulas.

Step 2: Write the first measure — Quantity Sold

1. In the Data panel, right-click Key Measures.
2. Click New Measure. A formula bar appears.
3. Type the name: Quantity Sold.
4. Type = after the name.
5. Start to type SUM. Power BI shows a suggestion. Press Tab.
6. Select the column: Sales[Quantity].
7. Close the bracket.

The formula:

Quantity Sold = SUM(Sales[Quantity])

1. Press Enter.

Write the table name with the column

In DAX, write the table name and the column name together: Sales[Quantity]. Do not write the column alone. Two tables can have columns with the same name.

The new measure has a small calculator icon. It is ready to use.

The new Quantity Sold measure next to Sum of Quantity. The values are the same. The red box shows the proof.

Step 3: Add the measure to a table

1. Click the table visual.
2. Check the Quantity Sold measure.
3. Remove the old Sum of Quantity column.

The result is the same as the implicit measure. But now you decide the numbers, not Power BI.

Step 4: Write Total Sales (row by row)

Quantity Sold adds one column. Total Sales multiplies two columns: price times quantity. You cannot use plain SUM for this. Use SUMX.

Why SUMX

SUM adds one column. SUMX works row by row. It multiplies the two values in each row, then adds the results.

1. Right-click Key Measures > New Measure.
2. Type this formula:

Total Sales = SUMX(Sales, Sales[Quantity] * Sales[Price])

1. Press Enter.
2. Add the measure to the table visual.

Step 5: Write Total Cost (lookup from another table)

The cost value is in the Products table. The quantity value is in the Sales table. Use RELATED to get the cost.

RELATED is a lookup

In Excel you use VLOOKUP. In DAX you use RELATED. RELATED follows the model relationship. It gets the value from the related table.

1. Right-click Key Measures > New Measure.
2. Type this formula:

Total Cost = SUMX(Sales, Sales[Quantity] * RELATED(Products[Cost]))

1. Press Enter.

The result must match the Excel calculation.

Step 6: Write Total Profit and Profit Margin

Profit = Sales minus Cost. Write the profit measure in one step:

1. New measure. Type this formula:

Total Profit = [Total Sales] - [Total Cost]

How to write a measure inside a formula

Write the measure name alone, inside square brackets: [Total Sales]. Do not write the table name for a measure.

1. Write the margin measure. Margin = Profit / Sales:

Profit Margin = [Total Profit] / [Total Sales]

1. For percent format, click the measure. Then click % in the format list.

Step 7: Write Total Transaction

1. New measure. Type this formula:

Total Transaction = COUNTROWS(Sales)

COUNTROWS counts the rows in the Sales table.

Step 8: Delete the empty column

The Key Measures table still has the empty Column 1 from Step 1.

1. Right-click Column 1.
2. Click Delete.
3. Click Yes to confirm.

The table is now empty in the table view. This is normal. Do not worry when the Key Measures table looks empty. The measures live in the formula bar, not in the table.

All measures of this class

Measure	Formula	What it does
Quantity Sold	SUM(Sales[Quantity])	Adds all quantities
Total Sales	SUMX(Sales, Sales[Quantity] * Sales[Price])	Price times quantity, row by row
Total Cost	SUMX(Sales, Sales[Quantity] * RELATED(Products[Cost]))	Quantity times product cost
Total Profit	[Total Sales] - [Total Cost]	Profit in ringgit
Profit Margin	[Total Profit] / [Total Sales]	Profit as a percent
Total Transaction	COUNTROWS(Sales)	Number of sales rows

Optional: let AI write the DAX for you

The class shows a method to ask an AI tool (ChatGPT, Copilot) for DAX formulas. The AI must first learn your data model. The TMDL script gives the model to the AI.

Step 1: Export the data model as a TMDL script

1. Go to Model view.
2. Right-click on the model background.
3. Click Script TMDL to Script Tab.
4. A long code window opens. It shows tables, columns, measures, and relationships.

The TMDL script window.

Step 2: Save the script in a text file

1. Press Ctrl+A to select all the code.
2. Press Ctrl+C to copy it.
3. Open Notepad (or any text editor).
4. Press Ctrl+V to paste the code.
5. Save the file. Name example: Power BI data model.txt.

The trainer's step list. The red numbers 1-10 show the full flow.

Step 3: Ask the AI

1. Open your AI tool.
2. Attach the text file.
3. Ask your question. Example: "Add Sales LY (last year) and Sales YTD (year to date) measures to Key Measures."
4. Copy the code the AI gives you.

Use only approved AI tools

Use an AI tool that your company permits. Do not upload company data to an unapproved tool.

Step 4: Test the AI code

1. In Power BI, open DAX Query View.
2. Add a new query. Paste the code from the AI.
3. Click the run button.
4. The result appears in a table below.

Check DEFINE at the top

The code must start with the word DEFINE. If the word is missing, add it. The query does not run without it.

Step 5: Verify the numbers

1. Compare the AI result with a number you can check by hand.
2. Example: Sales LY for 2018 is empty (no 2017 data in this dataset).
3. Sales LY for 2019 shows the 2018 values.

AI can be wrong

AI writes a formula that looks correct but is not always correct. Always check the result. If the AI fails, write the DAX by hand.

Format the measures

1. Click the measure name in the Data panel.
2. In the ribbon, find the format fields.
3. Profit Margin: click the % button.
4. Quantity Sold: set 0 decimal places (remove the comma).
5. Total Transaction: set 0 decimal places.

Measure	Format
Quantity Sold	Whole number
Total Sales	Whole number
Total Profit	Whole number
Profit Margin	Percentage, 0 decimals
Total Transaction	Whole number

Format the date column

1. Click the Date column in the Calendar table.
2. In the ribbon, open the format list.
3. Choose the date format. Example: dd/mm/yyyy.

Codes: M = month, Y = year, D = day, MMM = Jan, MMMM = January.

Format many measures at one time

1. Go to Model view.
2. Select the currency measures: Total Sales, Total Profit, Total Cost, Sales LY, Sales YTD. Hold Ctrl to select more than one.
3. In the Properties panel, open Format.
4. Select Currency. Set the decimal places to 0.
5. Select the format: English (US).

One change formats all the selected measures.

Optional: a calculated column with SWITCH

A calculated column makes a new column inside a table. Use the DAX function SWITCH for if/else logic.

1. Go to Data view.
2. Right-click the Sales table.
3. Click New Column.
4. Type this formula:

Price Category =
SWITCH(
    TRUE(),
    Sales[Price] < 100, "Cheap",
    Sales[Price] > 2000, "Expensive",
    "Normal Price"
)

How SWITCH works

SWITCH reads top to bottom. The first true condition wins. The last value is the "else" result.

Result in the data:

Price	Category
RM 50	Cheap
RM 500	Normal Price
RM 2500	Expensive

Calculated column or Power Query?

Point	Calculated column (DAX)	Power Query
Runs	In the Power BI engine (virtual)	At refresh time (physical)
Speed	Slower on big data	Faster, prepared in advance
Best for	Advanced logic	Simple, repeatable clean-ups
Edit	In DAX	With clicks in the UI

The preference: do the easy steps in Power Query. Use DAX for the advanced logic.

Next: Continue to 15. Visualizations.