Microsoft Learn

Measures and Calculations in Tabular Models

In Analysis Services tabular models, measures are dynamic calculations that are performed at query time. They are essential for aggregating data and providing meaningful insights. This document explains how to create and manage measures using DAX (Data Analysis Expressions).

What are Measures?

Measures are formulas that are defined in the model and can be used in reports and visualizations. Unlike calculated columns, which are computed row by row and stored in the table, measures are calculated on-the-fly based on the current context of the query. This makes them more flexible and efficient for complex aggregations and business logic.

Creating Measures

Measures are typically created using the DAX language. You can create measures directly in SQL Server Data Tools (SSDT) or in Power BI Desktop.

Using DAX Studio for Measure Creation (Advanced)

For more complex scenarios or to experiment with DAX, you can use tools like DAX Studio. However, for most development, creating measures directly within your modeling environment is sufficient.

Basic Measure Syntax

A basic measure definition in DAX follows this structure:

Measure Name = DAX Expression

Example: Calculating Total Sales

Let's say you have a 'Sales' table with a 'Quantity' column and a 'UnitPrice' column. To calculate the total sales, you can create a measure like this:

Total Sales = SUMX(Sales, Sales[Quantity] * Sales[UnitPrice])

In this example:

• Total Sales is the name of the measure.
• SUMX is an iterator function that iterates over each row of the 'Sales' table.
• Sales[Quantity] * Sales[UnitPrice] calculates the sales amount for each individual transaction.
• The results of the multiplication are summed up to give the total sales.

Common DAX Functions for Measures

Here are some commonly used DAX functions for creating measures:

• Aggregation Functions: SUM, AVERAGE, MIN, MAX, COUNT, DISTINCTCOUNT.
• Iterator Functions: SUMX, AVERAGEX, MAXX, MINX, FILTER. These functions allow you to iterate over rows and perform calculations based on specific conditions.
• Time Intelligence Functions: TOTALYTD (Year-to-Date), SAMEPERIODLASTYEAR, DATEADD. These are crucial for performing time-based comparisons.
• Logical Functions: IF, SWITCH, ISBLANK.
• Text Functions: CONCATENATE, FORMAT.

Calculated Columns vs. Measures

It's important to understand the difference between calculated columns and measures:

• Calculated Columns: Calculated once when the data is processed and stored in the table. They consume memory and can impact processing time. Use them when you need to derive a value for each row that doesn't depend on the query context.
• Measures: Calculated at query time and not stored. They are dynamic and adapt to the filters applied in reports. They are generally more efficient for aggregations and complex calculations.

Managing Measures

In your modeling environment, you can:

• Create New Measures: Right-click on a table or use the "New Measure" button.
• Edit Existing Measures: Double-click on a measure or select it and use the formula bar.
• Organize Measures: Use folders to group related measures, improving usability in reporting tools.
• Format Measures: Apply number formatting (e.g., currency, percentage, decimal places) to ensure consistent presentation.

Advanced Concepts

As you become more proficient with DAX, you'll explore advanced concepts such as:

• Row Context vs. Filter Context: Understanding these contexts is fundamental to writing correct DAX.
• CALCULATE Function: The most powerful function in DAX, allowing you to modify filter context and perform complex calculations.
• Relationships and RELATED/RELATEDTABLE: Leveraging model relationships to access data from other tables.
• Variables in DAX (VAR/RETURN): Improving readability and performance of complex DAX expressions.

Conclusion

Measures are the backbone of business intelligence solutions built with Analysis Services tabular models. Mastering DAX is crucial for creating robust, flexible, and insightful data models.