Microsoft Docs

Create Measures in Azure Analysis Services

Measures are calculations that aggregate data from your tables. They are often used in business intelligence reports to perform calculations like sums, averages, and counts. In Azure Analysis Services, you can create measures using DAX (Data Analysis Expressions).

Understanding Measures

Measures are dynamic. Their values are calculated based on the context in which they are used, such as filters applied in a report. This is different from calculated columns, which are computed row by row and stored in the table.

Creating a Simple Measure

To create a basic measure, you'll typically use an aggregation function like SUM, AVERAGE, MIN, or MAX. Let's create a measure to calculate the total sales amount.

Steps:

1. Open your Azure Analysis Services model in Visual Studio with the Analysis Services projects extension, or using SQL Server Management Studio (SSMS) connected to your model.
2. In the Model Designer, right-click on the table where you want to add the measure (e.g., the 'Sales' table).
3. Select "New Measure".
4. In the formula bar that appears at the top of the Model Designer, enter the following DAX expression:

   Total Sales = SUM(Sales[SalesAmount])

5. Press Enter to create the measure.

In this DAX formula:

• Total Sales is the name of the measure.
• SUM() is the DAX aggregation function.
• Sales[SalesAmount] refers to the 'SalesAmount' column in the 'Sales' table.

Creating More Complex Measures

DAX allows for sophisticated calculations. Here are some examples:

Year-over-Year Sales Growth:

This measure calculates the difference in sales compared to the same period in the previous year.

Sales YoY Growth =
VAR CurrentSales = SUM(Sales[SalesAmount])
VAR PreviousYearSales = CALCULATE(SUM(Sales[SalesAmount]), SAMEPERIODLASTYEAR('Date'[Date]))
RETURN
    CurrentSales - PreviousYearSales

Average Sales per Order:

This measure calculates the average sales amount for each individual order.

Avg Sales per Order =
AVERAGE(Sales[SalesAmount])

Sales Amount vs. Target:

This measure calculates the ratio of actual sales to a target value (assuming you have a target measure or column).

Sales vs Target Ratio =
DIVIDE(SUM(Sales[SalesAmount]), [Sales Target Measure])

Measure Formatting

You can format your measures to display numbers in a user-friendly way (e.g., currency, percentages). Select the measure in the Model Designer and use the Formatting options in the Properties window.

Common Formatting Options:

• Number Format (e.g., Currency, Percentage, Number)
• Decimal Places
• Display Folder (to organize measures in your BI tools)

Best Practices for Measures:

• Use Clear Names: Measure names should be descriptive and intuitive.
• Leverage DAX Functions: Explore the extensive library of DAX functions for powerful calculations.
• Test Thoroughly: Always test your measures with sample data and in different reporting contexts.
• Consider Performance: Complex measures can impact query performance. Optimize your DAX formulas.
• Use Variables: For complex formulas, use variables (VAR...RETURN) to improve readability and performance.