DAX is used to:
- Create calculated columns (new columns in a table).
- Create measures (dynamic calculations like SUM, AVERAGE).
- Create calculated tables (tables derived from other tables).
- Build complex logic for filters, aggregations, ranking, time intelligence, etc.
🔹 DAX Basics
Here are the main components:
1. Calculated Column
Added row-by-row in a table.
FullName = Customers[FirstName] & " " & Customers[LastName]2. Measure
Calculates value dynamically (based on filters, slicers, etc.).
Total Sales = SUM(Sales[Amount])3. Calculated Table
TopCustomers =
TOPN(10, Customers, Customers[TotalSales], DESC)🔹 Common DAX Functions
| Category | Example |
|---|---|
| Aggregation | SUM, AVERAGE, COUNT, MAX, MIN |
| Logical | IF, AND, OR, SWITCH |
| Text | LEFT, RIGHT, CONCATENATE, SEARCH |
| Filter Context | CALCULATE, FILTER, ALL, REMOVEFILTERS |
| Time Intelligence | DATESYTD, SAMEPERIODLASTYEAR, TOTALYTD |
| Ranking | RANKX, TOPN, EARLIER |
Example 1: Total Sales for 2024
TotalSales2024 =
CALCULATE(
SUM(Sales[Amount]),
YEAR(Sales[Date]) = 2024
)
🔹 Example 2: Sales Growth %
SalesGrowth =
DIVIDE(
[Total Sales] - [Last Year Sales],
[Last Year Sales],
0
)
🔹 Where to Use DAX in Power BI?
- Model View → Right-click a table → New column, New measure, or New table.
- Report View → Use measures in visualizations.
- Data View → See calculated columns in action.
