Calculated Columns
Master calculated columns in DAX
What You'll Learn
- When to use calculated columns
- Create calculated columns with DAX
- Common calculated column patterns
- Performance considerations
What are Calculated Columns?
Calculated columns add new columns to your tables using DAX formulas.
Characteristics:
- Evaluated row-by-row
- Stored in data model
- Created during refresh
- Can be used in slicers/filters
- Takes up memory
Creating Calculated Columns
Method 1: Right-click table
- Right-click table in Fields pane
- New Column
- Enter formula
- Press Enter
Method 2: Modeling tab
- Select table
- Modeling > New Column
- Enter formula
Method 3: Table tools
- Click table
- Table Tools > New Column
Common Patterns
Text Concatenation
Combine First + Last Name: FullName = [FirstName] & " " & [LastName]
Add prefix: Product Code = "PROD-" & [ProductID]
Combine with separator: Address = [Street] & ", " & [City] & ", " & [State]
Extracting Text
First 3 characters: Code = LEFT([ProductName], 3)
Last word: LastName = RIGHT([FullName], SEARCH(" ", [FullName], 1, -1))
Date Calculations
Extract year: Year = YEAR([OrderDate])
Extract month name: Month = FORMAT([OrderDate], "MMMM")
Extract quarter: Quarter = "Q" & QUARTER([OrderDate])
Day of week: DayOfWeek = FORMAT([OrderDate], "dddd")
Fiscal year (starts July): FiscalYear = IF(MONTH([OrderDate]) >= 7, YEAR([OrderDate]) + 1, YEAR([OrderDate]))
Math Calculations
Profit: Profit = [Revenue] - [Cost]
Margin percentage: Margin% = DIVIDE([Profit], [Revenue])
Price with tax: PriceWithTax = [Price] * 1.1
Discounted price: DiscountedPrice = [Price] * (1 - [DiscountPercent])
Conditional Logic
Simple IF: Status = IF([Amount] >= 1000, "VIP", "Regular")
Multiple conditions: Tier = IF([Amount] >= 5000, "Platinum", IF([Amount] >= 2000, "Gold", IF([Amount] >= 1000, "Silver", "Bronze")))
Using AND: Eligible = IF([Age] >= 18 && [Score] >= 70, "Yes", "No")
Using OR: Special = IF([Country] = "USA" || [Country] = "Canada", "North America", "Other")
Lookups
RELATED function: Get value from related table:
Product Category = RELATED(Products[Category])
Must have relationship between tables!
Example: In Sales table, get Product Name from Products table: ProductName = RELATED(Products[ProductName])
Ranking
RANKX: Rank = RANKX(ALL(Products), [TotalSales])
Gives rank based on sales amount
When to Use Calculated Columns
Good uses:
- Grouping/categorizing data
- Date parts (year, month, quarter)
- Text manipulation
- Creating filter columns
- Simple row-level calculations
Avoid for:
- Aggregations (use measures!)
- Dynamic calculations
- Large datasets (memory impact)
Performance Tips
Calculated columns:
- Increase model size
- Slow down refresh
- Evaluated for every row
Alternatives:
- Add in Power Query (better performance)
- Use measures instead (when possible)
- Calculate in source system
When calculated column is OK:
- Small tables (< 100k rows)
- Complex logic not possible in Power Query
- Need to filter/slice by result
Calculated Columns vs Power Query
Use Power Query when:
- Simple transformations
- Static calculations
- Better performance needed
Use DAX when:
- Need relationships (RELATED)
- Complex business logic
- Calculated columns reference other calculated columns
Try This Exercise
Add these calculated columns:
-
In Sales table: Year = YEAR([OrderDate]) Month = FORMAT([OrderDate], "MMM")
-
In Products table: PriceRange = IF([Price] >= 100, "Premium", IF([Price] >= 50, "Mid", "Budget"))
-
In Customers table: FullName = [FirstName] & " " & [LastName]
-
Test: Use PriceRange in a slicer Verify it filters visuals correctly
Debugging Calculated Columns
Formula returns error:
- Check column names (case-sensitive!)
- Verify data types match
- Look for null values
Blank results:
- Columns might contain nulls
- Use ISBLANK() to check
Wrong results:
- Test with simple data first
- Break complex formula into steps
Next Steps
Now let's learn about Measures - the real power of DAX!
Tip: Most calculations should be measures, not calculated columns!