🔢 The Essential DAX Toolkit

Now that you understand what a Measure is (from the previous chapter), it is time to build your vocabulary. DAX contains over 250 functions, but you will use these core functions 90% of the time.

DAX syntax is very strict. Every function requires you to open and close parentheses (), and separate multiple arguments with a comma ,. When referencing a column, it is best practice to include the table name too: 'Table Name'[Column Name].

➕ Aggregation Functions

These functions take an entire column of numbers and crush them down into a single result.

• SUM(ColumnName) Adds up all the numbers in a column. Example: Total Revenue = SUM('Sales'[Revenue])

• AVERAGE(ColumnName) Finds the mathematical mean of a column.

• MIN(ColumnName) / MAX(ColumnName) Finds the lowest or highest value in a column. Great for finding the earliest date or the most expensive product.

• DIVIDE(Numerator, Denominator, [AlternateResult]) Always use DIVIDE instead of the / symbol! If the denominator is zero, the / symbol will crash your visual with an "Infinity" error. The DIVIDE function safely handles division by zero and lets you provide an alternate result (like returning "0" or "Blank"). Example: Profit Margin = DIVIDE([Total Profit], [Total Sales], 0)

🔢 Counting Functions

Data analysis heavily relies on counting occurrences (e.g., "How many orders were placed today?").

• COUNT(ColumnName) Counts the number of cells in a column that contain numbers. (It ignores text and blanks).

• COUNTA(ColumnName) Counts the number of cells that contain anything (text, numbers, booleans) as long as it is not blank.

• COUNTROWS(TableName) Simply counts the total physical rows in a table. Highly recommended because it is incredibly fast for the engine to calculate.

• DISTINCTCOUNT(ColumnName) Counts how many unique items exist in a column. Example: If a customer buys 5 times in a month, COUNTROWS will return 5. But DISTINCTCOUNT('Sales'[CustomerID]) will return 1, because it's only 1 unique customer!

🧠 Logical Functions

Sometimes you need a measure to make a decision based on the data.

• IF(Condition, TrueResult, FalseResult) Checks a condition. Example: Bonus = IF( SUM('Sales'[Amount]) > 1000, "Bonus Earned", "No Bonus" )

• SWITCH(Expression, Value1, Result1, Value2, Result2, ..., DefaultResult) Use SWITCH when you have a massive nested IF statement. It is much cleaner to read! Example: MonthName = SWITCH([MonthNumber], 1, "Jan", 2, "Feb", 3, "Mar", "Unknown")

• AND() / OR() / NOT() Used to combine multiple logical conditions together inside an IF statement.

🏋️ Practice Drill

Let's use DIVIDE to calculate a safe percentage!

The Smart Way: Instead of writing Profit Margin = SUM('Sales'[Profit]) / SUM('Sales'[Revenue]) (which might crash if Revenue is missing), let's use the DAX native function.

Task:

// Try answering these:
1. Create a new Measure in your model.
2. Assuming you already have a `Total Sales` measure and a `Total Cost` measure, write the following:
   `Margin % = DIVIDE([Total Sales] - [Total Cost], [Total Sales], 0)`
3. Once the measure is created, click on it in the Data Pane. Then look at the top menu ribbon under "Measure Tools" and click the `%` icon to format it as a percentage!

← 🧮 Introduction to DAX | Next Topic → 🔧 Modifying DAX Context