What is DAX?

DAX, the Data Analysis Expressions is a powerful formula expression language that includes functions, operators, and constants to perform simple to advanced calculations and queries on data from different data sources to return values. DAX helps to perform complex calculations and analyze data trends in real-world business scenarios.

Syntax

Syntax: Name of DAX Measure = FunctionName(TableName.Column) or Name of Measure = FunctionName (TableName.Column1 Expression TableName.Column2)

Parameters:

• Name of DAX Measure
• The ‘=’ sign, which indicates the beginning of the formula
• A Function, which performs the calculation
• The Parentheses ‘( )’
• Table and column reference with Scalar operators (+,-,*,/) if any

Prerequisites

DAX Measures can be created and used from Power BI, so a basic working knowledge of Power BI Desktop is required. Working knowledge of Formulas in Microsoft Excel can be helpful but understanding how a formula is written is essential.

Example:

1.Maximum Forecast By Country = MAX(Forecast[Forecast])

2.ProfitOnSales = Sum(‘Order Data'[Revenue]) – SUM(‘Order Data'[Cost])

What is Measure?

A measure is a calculation done on data field, which can perform an arithmetic calculations or aggregate functions. In Power BI, measures are used to create variety of DAX functions like SUM, AVERAGE, MAX, MIN and COUNT.

Examples: Measures include: Total Sales, Total Salaries, Average Quantity, Average Order Value, Count of Customers, Year-to-Date (YTD) Sales and so on.

Measure Table

A Measure Table in Power BI is a special table created from Power BI using the Create new Table option and this table is used to store a collection of all DAX Measures created from Power BI.

Creating Measure Table

To create a new Measure Table, click ‘Create New Table’ button from the menu bar in ‘Model View’ mode. Give a Table Name and click ‘Load’ button to create a new measure table.

Create New Measure from Measure Table

We can create new measures and add it to the Measure table. Right click on the new measure table to create a New Measure. Below is an example of Measures in a Measure Table and how a new Measure is created with DAX Measure.

How to create a DAX Measure in Power BI?

1. Open Power BI desktop version

2. Import Data from a Data Source like Excel, SQL Server, Access, Oracle, MySQL or any other data set like XML, JSON, Text File

3. Once the data source is loaded, the Fields list will be added to the right of the window.

4. Create a New DAX Measure by clicking the ‘New Measures’ button at the tool bar at the top

5. Input New Measure Name, default will be ‘Measure’ and add ‘=’ sign

6. Add Function Name like Sum, Average, MAX, MIN or any other calculation function and open a bracket ‘(‘.

7. Select the Column name from the list of columns after typing in the table name and close the bracket ‘)’

8. The new measure is ready. The new measure will be displayed with the fields list.

9. Use the Measure to create Visualization in Power BI as below.

DAX (Data Analysis Expressions) Measures in Power BI are standard aggregation functions or dynamic custom expressions defined with the DAX formula language. Using this formula language, you can calculate, combine, and filter data that can return one or more values. DAX Measures are dynamic and user-defined formulas where results change depending on context. This article discusses the DAX Measures in Power BI, how to create and edit, and how to use them in Power BI Visualization reports.