Calculated Columns vs Measures

Both are created using DAX, but they serve very different purposes and behave differently.

Calculated Columns

• Created using DAX formulas in the Data View • Computed row by row — each row gets its own value • Stored in the data model (increases file size) • Evaluated during data refresh • Can be used for filtering, slicing, and in relationships

Creating a Calculated Column:

1. Go to Data View
2. Click New Column in the ribbon
3. Write a DAX formula in the formula bar

Example:

Profit = [Revenue] - [Cost]

Example with IF:

Performance = IF([Sales] > 10000, "High", "Low")

Measures

• Created using DAX formulas in the Report View or Data View • Computed dynamically based on the current filter context • NOT stored row by row — calculated on the fly • Do NOT increase file size significantly • Used for aggregations displayed in visuals

Creating a Measure:

1. Click New Measure in the ribbon
2. Write a DAX formula in the formula bar

Example:

Total Sales = SUM(Sales[Amount])

Example with CALCULATE:

East Sales = CALCULATE(SUM(Sales[Amount]), Region[Name] = "East")

Key Differences

When to Use Which?

Use Calculated Column when: • You need a new category or label for each row • The value won't change with filters (e.g., Profit per row) • You need to use it in a slicer or filter

Use Measure when: • You need a dynamic aggregation (Sum, Average, Count) • The result should change when filters are applied • You're calculating KPIs, percentages, or running totals

Working with Composite Models

Composite models allow you to combine Import and DirectQuery data sources in the same Power BI report.

What is a Composite Model? • Uses multiple connection modes simultaneously • Some tables are imported (cached locally) • Other tables use DirectQuery (queried in real-time)

Why Use Composite Models? • Combine high-performance imported data with real-time DirectQuery data • Use imported lookup/dimension tables with large DirectQuery fact tables • Reduce data refresh times by importing only small tables

How to Create a Composite Model:

1. Import data from a source (e.g., Excel dimension tables)
2. Add a DirectQuery source (e.g., SQL Server fact table)
3. Power BI prompts you to switch to a composite model
4. Create relationships between imported and DirectQuery tables

Storage Modes in Composite Models:

Best Practices: • Set small dimension tables to Import mode • Keep large fact tables in DirectQuery mode • Use Dual mode for tables referenced by both Import and DirectQuery tables • Test performance carefully — mixing modes can create complex query plans