#### How to Use DAX (Data Analysis Expressions) Functions

DAX expressions (Data Analysis Expressions) is the formal language for writing formulas in Power Bi, Power Pivot and in Excel data models. This language is made up of functions, constants, and operators. You can use DAX to define custom calculations for Calculated Columns and for Measures (also known as calculated fields). It also allows you to calculate values, create relationships between tables, and more.

In this Excel tutorial lesson, we will discuss what DAX Expressions are and how you can use them in your Excel spreadsheets. We will also look at some of the benefits of using DAX Expressions and explore a few real-world applications. Finally, we will provide you with some information on how to get started using DAX Expressions in your spreadsheets.

## Why Use DAX Expressions?

There are many benefits to using DAX Expressions in your Excel spreadsheets. DAX Expressions offer several advantages over other methods of data analysis and modeling.

- DAX expressions are easy to learn and use.
- They allow you to perform complex calculations on your data with ease.
- They make it easy to create relationships between tables.
- They are very efficient, meaning that they can handle large data sets without slowing down your computer.
- They allow you to use formulas across more than one table and to make formulas by using existing formulas.

## What Information Do You Need to Know to Get Started Using DAX Expressions in Your Spreadsheets?

If you want to start using DAX expressions in Excel, there are a few things you need to know first. First, you need to have a basic understanding of how DAX expressions work. Second, you need to have a data set that you can use to practice creating DAX expressions. With these things in hand, you are ready to start using DAX expressions in your spreadsheets.

## Preparing to Create DAX Expressions

### Getting Data | Creating A Query

To create DAX expressions in Excel, we first need to load the data from your table(s) into the Data Model. For this article, we have sample data in the file – How To Use DAX (Data Analysis Expressions) Functions In Excel.xlsx**.**

Open the sample data file. Click any cell in the **Q1Data** table, on the ribbon, click **Data, Get & Transform Data** group, click **From Table/Range.**

If you are seeing the **Create Table** dialog, this means your cursor was outside the table. Select your table including the headers, click **My table has headers, **click **Ok**.

The Power Query** Editor** opens with the **Q1Data** table displayed. You can use the query at this stage to edit the table as you wish – removing duplicate records/columns, adjusting format of text/numbers, etc. – so only the required data will be loaded into the Data model.

### Loading Tables Into A Data Model

Once the query has been edited to your requirements, we can load the data into the data model. Using the **Close & Load To **option we can also create a Pivot Table at the same time.

On **Home** tab, click **Close and Load To…**

The **Import Data **dialog opens, select **PivotTable Report, New worksheet **and** Add this data to the Data Model, **click **Ok**.

The** Power Query Editor **closes, you are back in Excel on a new tab created for the PivotTable. The **PivotTable Fields** pane is active. On the right of the window the **Queries & Connections **pane is open. The Connections tab is visible.

Rename this worksheet tab **Q1Data PT**,** **to differentiate it from table tab.

### Getting Data, Creating A Query, Loading Data For Subsequent Tables.

Repeat the steps for all the tables you wish to include in your data model. In this case, we need to repeat the steps above for the **Region** table too.

- Click any cell in the
**Region**table, on the ribbon, click**Data, Get & Transform Data**group, click**From Table/Range.**If you are seeing the**Create Table**dialog, this means your cursor was outside the table. Select your table including the headers, click**My table has headers,**click**OK.** - The
**Power Query Editor**opens with the**Region**table displayed. Any other tables are included in this query are in the left pane. You can use the query at this stage to edit the table as you wish – removing duplicate records/columns, adjusting format of text/numbers, etc. – so only the required data will be loaded into the Data model.

**Loading Tables Into A Data Model**

- On
**Home**tab, click**Close and Load To…** - The
**Import Data**dialog opens, select**PivotTable Report, New worksheet**and**Add this data****to the Data Model,**click**OK.**

The** Power Query Editor **closes, you are back in Excel on a new tab created for the PivotTable; the **PivotTable Fields** pane is active. The **Queries & Connections **pane is on the right; the Connections tab is visible.

Rename this worksheet tab **Region PT, **to differentiate it from table tab.

### Creating A PivotTable From A Data Model

On the the **Q1Data PT **tab, click in the PivotTable template area to view the **PivotTable Fields** Click the **Product** field checkmark, Excel places Product into the **Rows** area and on the PivotTable.

## Creating A DAX Expression Through Measures

### DAX Expressions Syntax

Before we create an expression, let’s review the syntax of DAX and the aspects that make up a formula.

The syntax for DAX expressions is **=FUNCTION(TableName[Column name]). **

#### Measures

- Formulas created using DAX expressions are known as
**Measures**. Measures can be entered/created in formula bars or in Measure dialog boxes which we will see later. - Measure names must always be in square brackets [].
- Measure names can contain spaces.
- Measure name cannot be repeated within a model. Thus, it is not necessary to include the table name in a measure.

#### Functions

- Once you press equals (=), Excel presents you with a list of DAX functions that start with the letter you typed.
- Here’s a list of DAX functions https://docs.microsoft.com/en-us/dax/dax-function-reference

#### Table Name

- Enter all table names in brackets ()
- Unlike with Excel formulas you must enter the closing bracket when creating DAX expressions or you will receive an error.

#### Column Names

- Enter column names in square brackets [].
- When entering column names, a list of columns within the model appears for your selection.
- In Excel, we apply formulas to individual cells or ranges of cells. In DAX the formulas refer to columns of data or tables instead of cells.
- The names of your columns must be unique within your table; however, you can have multiple tables with identical column names, just include the table names to differentiate. For example, you can have two different columns name Sales, when using them in an expression use the table name to specify which of them you are referring to =Sum(Q1Table[Sales])+ (Q2Table[Sales]).

## Creating A Measure

We are going to create a measure through the PivotTable, to count the units per order.

In the **PivotTable Fields** pane, right-click on the **Q1Data** table name, click **Add Measure**.

The **Measure **window opens, make the following entries:

**Table name**– Q1Data, you could change the table here if required**Measure Name –**enter**Units****Value Description**– enter**Units per order**

In the formula box we will enter the measure, once you type in the function a list of the tables and fields from the Data Model that is linked to this PivotTable appears.

You can adjust the zoom in this field if the text is too small, hold down CTRL and push the rollerball on your mouse forward.

Type **=SUM(**

Select the Table and column you want, in our case **Q1Data[Sales]**

The full expression is **=SUM(Q1Data[Sales]) – **unlike with regular Excel formulas, you must enter the closing bracket or you’ll get an error.

Enter the closing bracket.

In **Category, **click **Number. **Click** Use 1000 separator, OK.**

In the **PivotTable Fields** you’ll now see the **Units** measure under the list of columns.

Click the checkmark beside the **Units** measure; Excel adds it into the **Value** The PivotTable now includes the Units measure. The values here are total sales for each of the Product.

This measure allows us to view the subtotal of all the sales by product.

Let’s create a few more measures. Say a sales manager for this team wants to count the number of orders in the Q1Data table; we are counting each entry as an order in this example. They can create a measure to do that.

Right-click on the table name, click** Add Measure**. In the **Measure** window enter **Number of Orders**.** Value description – **counts each row as an order. Enter** =countrows(**

Type the letter ‘q’, click on the **Q1Data** name that appears, close the bracket. We don’t need to include and column name in this formula as its only counting the number of rows in the table.

The full expression is **=COUNTROWS(Q1Data)**. Under **Number **click **Use 1000 separator, **click **Ok**.

The **Number of orders** measure now appears in the **PivotTable Field** list, click the checkbox to add the field to the PivotTable; Excel adds it into the **Values** area.

What we can see now is that for Laptops there were 4 orders with a combined total of **501** units.

### Creating Formulas With DAX Expressions

Say a manager wants to know the average units per order, we can create a measure with a formula to give us that data.

Here’s where an advantage of DAX expressions comes in, we can use existing measures in a new measure/formula. In this case we’ll use the Number of orders measure to get the average units per order.

Right-click on the table name, click** Add Measure**

In the **Measure** window, **Measure Name** enter **Ave units per order**

In the **Value Description** field enter **Units/Number of orders**

In the measure pane enter** =[**

Excel lists the units measure as **fx Units**, select it.

Enter **/[n** and select the **Number of orders** measure.

Under Number click **Use 1000 separator**, select **2** in **Decimal Places** click OK.

Tick the **Ave units per order** This column is added to the PivotTable.

Though we used division to create a new formula with existing measures, we can create formulas using other operators – addition, subtraction and so on.

### Reusing A Formula/Measure

So currently we have the Average units per order by Product. We can use this measure to show the Average units per order by Salesperson.

Deselect **Product, Units **and** Number of orders**.

Select **Salesperson**. Now we have the average units per order by **Salesperson** by reusing the formula/measure created earlier.

### Linking Data Tables | Creating Relationships Via Diagram View

The manager would like to know the average units per order by region, to compare the effectiveness the sales team in each region. The list of salespersons and the region they’re responsible for is in a separate table called Region.

The manager can use the **Ave units per order** measure for this comparison but first we need to create a link between the Q1Data and Region tables.

Click in the PivotTable. On the ribbon, **Power Pivot **tab, click **Manage**.

The** Power Pivot for Excel** window opens, there is a tab for **Q1Data** and **Region**.

On the **Home** tab, **View** group, click **Diagram view**.

Click on **Salesperson** in the **Q1Data** the label turns green, drag the cursor to the **Salesperson **field in the **Region** table.

The link is created, it starts out highlighted green, then changes to a one-to-many relationship, i.e., for each salesperson listed once in the Region table, they appear multiple times in the Q1Data table.

Close the **Power Pivot for Excel** window. In the **Q1Data PT** tab, click in the PivotTable. In the **PivotTab**le **Fields** window click **All, **Excel displays the related tables.

Click the **Q1Data **listing with the little red query icon, deselect **Salesperson**. Open the **Region** list with the little red query icon, select **region**.

We now have the average units per order by region.

### How to Edit/Delete Measures/DAX Expressions

Should you need to edit a measure there are a couple of ways to do this.

To edit a measure in the** PivotTable Fields **pane. Right-click on the measure, select **Edit Measure, **make your changes.

In the **Power Pivot **tab, click **Measures, Manage Measures**.

Select the measure to edit, click **Edit, note **you can also delete a meaure in this view.

The measures window opens, edit as needed, Click **Ok**.

## What Are the Real-World Applications of DAX Expressions?

DAX Expressions can be used for a variety of purposes. For example, you can use them to calculate values, create relationships between tables, and more. In the business world, DAX expressions are often used to create financial models. In the academic world, they may be used to analyze data sets in research studies.

DAX Expressions are a powerful tool that you can use in Excel to analyze and model data. In this blog post, we discussed what DAX Expressions are and how you can use them in your Excel spreadsheets. We also looked at some of the benefits of using DAX Expressions and explored some real-world applications. Finally, we provided you with some information on how to get started using DAX Expressions in your own spreadsheets! With this information, you are now ready to start using DAX expressions in your own Excel spreadsheets. Give it a try and see what you can create!

Do you have any tips for using DAX Expressions in Excel? And be sure to check out our other Excel tutorial posts for more tips and tricks on using Microsoft Excel. Thanks for reading!

## Leave a Reply