DAX Basics
Understanding DAX (Data Analysis Expressions)
Description: DAX is the native formula and query language used throughout Power BI. It is a collection of functions, operators, and constants that can be used in a formula, or expression, to calculate and return one or more values. Think of it as "Excel formulas on steroids"—it looks similar but is designed to handle complex data relationships and massive datasets.
Why: While Power BI can perform basic sums automatically, DAX is what allows you to create sophisticated business logic. Whether you need to calculate year-over-year growth, profit margins, or dynamic running totals, DAX is the tool that makes it possible.
DAX Syntax Breakdown
Every DAX formula follows a specific structure. Using the right syntax ensures your calculations are accurate and easy to read.
Example Formula:
Total Profit = SUM(Sales[Profit])
This measure adds every value found in the Profit column of the Sales table.
Calculated Columns vs. Measures
Description: In Power BI, there are two primary ways to create new data using DAX. Choosing the right one is critical for report performance and accuracy.
Calculated Columns
Calculated row-by-row during data refresh. The result is stored in the model and increases file size.
Example: Year = YEAR(Sales[OrderDate])
Use when: You need to use the result as a Slicer or a Row/Column header.
Measures
Calculated "on the fly" when a visual is rendered. Results change dynamically based on user filters.
Example: Average Sales = AVERAGE(Sales[SalesAmount])
Use when: You are calculating aggregates (Sums, Averages, Counts) for charts.
The Power of Dynamic Measures
Measures are generally more powerful because of Filter Context. If you put a "Total Sales" measure in a Card visual, it shows sales for the whole company. If you then add a Slicer for "Country" and click "India," the measure automatically recalculates to show only India's sales without you needing to change the formula.
Conditional Logic: IF and SWITCH
Description: These functions allow your reports to make decisions based on data. They are essential for categorizing values, creating custom KPIs, and simplifying complex data into readable labels.
| Function | Syntax & Logic | Best For... |
|---|---|---|
| IF | IF(Condition, TrueValue, FalseValue) |
Simple "Yes/No" or "High/Low" checks. |
| SWITCH | SWITCH(TRUE(), Condition1, Result1, Condition2, Result2, Default) |
Multiple conditions (like grading A, B, C, D) in a single, readable formula. |
Practical Example:
Grade = SWITCH(TRUE(), [Score] >= 90, "A", [Score] >= 75, "B", "C")
This formula checks the score: 90+ gets an A, 75+ gets a B, and anything else gets a C.
Key Notes
- Memory Management: Use measures whenever possible. Too many calculated columns can slow down your report and make the file size unnecessarily large.
- DAX Formatting: Always use the "Format DAX" button or use Shift+Enter to put different parts of the formula on new lines. Readable code is easier to debug!
- The "TRUE()" Pattern: Using
SWITCH(TRUE(), ...)is a pro-tip that allows you to evaluate multiple different columns or measures within a single calculation.
🏋️ Test Yourself With Exercises
Take our quiz on DAX Basics to test your knowledge.
Browse Quizzes »