Skip to main content

Working with DAX

Tom S
Updated

DAX (Data Analysis Expressions) is the formula language used in Smartload to create calculated columns and measures. This guide covers DAX fundamentals, common patterns, and best practices for building effective calculations.

SUM( ) CALCULATE( ) FILTER( )
IF( ) RELATED( ) SUMX( )
DIVIDE( ) AVERAGE( ) COUNT( )
FORMULA LANGUAGE

Working with DAX

Power your analysis with calculated columns and measures

 

What is DAX?

DAX is Microsoft's formula language for tabular data models. It's used in Power BI, Excel Power Pivot, and Analysis Services—including Smartload cubes. DAX allows you to create custom calculations that go beyond simple sums and counts.

What You Can Do with DAX

🧮Calculate profit margins and ratios
📅Create year-over-year comparisons
🏷️Categorize data with IF/SWITCH logic
🔗Pull values from related tables

 

Calculated Columns vs. Measures

Understanding when to use each type is crucial for both accuracy and performance.

Column Calculated Column

  • Computed once during processing
  • Stored in the model (uses memory)
  • Row-by-row calculation
  • Can be used in filters and slicers
  • Best for: Categorization, concatenation, row-level values

Measure Measure

  • Computed at query time
  • Not stored (no memory overhead)
  • Responds to filter context
  • Changes based on user selections
  • Best for: Aggregations, KPIs, dynamic calculations
⚠️
Performance Impact

Calculated columns increase cube size and processing time. Excessive or complex calculated columns can significantly slow down processing and Excel performance. When possible, use measures for aggregations—they're computed on-demand and don't add storage overhead.

 

Common DAX Patterns

Text Concatenation

Combine multiple columns into one:

FullName = [FirstName] & " " & [LastName]

Conditional Logic

Categorize values based on conditions:

SizeCategory = 
IF([Revenue] > 1000000, "Large",
IF([Revenue] > 100000, "Medium", "Small"))

Safe Division

Avoid divide-by-zero errors:

GrossMargin = DIVIDE([Revenue] - [Cost], [Revenue], 0)

Profit Calculation (Measure)

Dynamic aggregation that responds to filters:

Total Profit = SUM([Revenue]) - SUM([Cost])

Related Table Lookup

Pull values from connected attribute tables:

CustomerRegion = RELATED(Customer[Region])

 

Key DAX Functions

Function
Purpose
Example
SUM
Add up all values in a column
SUM([Sales])
AVERAGE
Calculate mean value
AVERAGE([Price])
COUNT
Count non-blank values
COUNT([OrderID])
DIVIDE
Safe division (handles zero)
DIVIDE([A],[B],0)
IF
Conditional logic
IF([X]>0,"Yes","No")
RELATED
Get value from related table
RELATED(Cust[Name])
CALCULATE
Evaluate with modified filters
CALCULATE(SUM([X]),Filter)

 

Best Practices

✅ Do This

  • Use DIVIDE() instead of / for division
  • Name measures clearly (e.g., "Total Sales", "Gross Margin %")
  • Use measures for aggregations whenever possible
  • Test formulas with sample data first
  • Document complex formulas with comments

❌ Avoid This

  • Creating calculated columns for simple aggregations
  • Overly complex nested IF statements
  • Ignoring blank/null handling
  • Duplicating logic across multiple formulas
  • Using calculated columns when measures would work

 

Next Steps

If you're migrating from MDX-based cubes, the next guide covers key differences and translation strategies.

 

DAX is a powerful language that becomes intuitive with practice. Start with simple calculations, leverage the DIVIDE function for safe math, and prefer measures over calculated columns for aggregations. As you become comfortable, explore CALCULATE and time intelligence functions for more sophisticated analysis.

Related to

Related articles

Comments

0 comments

Article is closed for comments.