Using DAX in Excel, Power BI, and SQL

DAX is the native formula and query language for Microsoft Power Pivot (Excel), Power BI Desktop, and SQL Server Analysis Services (SSAS) tabular models. Thus, DAX can be used in any of these programs.

Getting ready

Ensure that you have Power Pivot for Excel, Power BI Desktop, or an SSAS tabular cube.

How to do it...

Depending upon the program being used, where DAX is entered varies somewhat. Use the following instructions according to the program you are using.

Excel

Perform the following steps to enter DAX in Excel:

1. Open Excel and create a data model using the Power Pivot tab in the ribbon and then click Add to Data Model. You must have a data model created before you can enter DAX.
2. To create a measure in Excel, select the Power Pivot tab from the ribbon, and then choose Measures followed by New Measure. A new window called Measure will appear where you can enter a DAX formula in the Formula area.

3. To create a new calculated column in Excel, select the Power Pivot tab from the ribbon and then Manage. In the Power Pivot window, select Design from the ribbon and then Add in the Columns section. A formula bar will appear just below the ribbon and your cursor will become active in the formula bar.

Power BI

Perform the following steps to enter DAX in Power BI Desktop:

1. Open Power BI Desktop.
2. In Power BI Desktop, select the Modeling tab from the ribbon and then choose New Measure | New Column or New Table.

The formula bar will appear just below the ribbon and your cursor will become active in the formula bar.

SQL Server

Perform the following steps to enter DAX in SQL Server:

1. Open SQL Server Management Studio.
2. In SQL Server Management Studio, connect to a deployed tabular Analysis Server data model.
3. Right-click the database in the deployed tabular model and choose New Query and then MDX.
4. When writing your DAX query, ensure that you begin your query with the EVALUATE keyword.

See also

For more details regarding this recipe, refer to the following links:

• Data Analysis Expressions (DAX) in Power Pivot: https://support.office.com/en-us/article/Data-Analysis-Expressions-DAX-in-Power-Pivot-BAB3FBE3-2385-485A-980B-5F64D3B0F730
• DAX basics in Power BI Desktop: https://docs.microsoft.com/en-us/power-bi/desktop-quickstart-learn-dax-basics
• Getting started with the DAX queries for SQL Server Analysis Services: https://www.mssqltips.com/sqlservertip/4068/getting-started-with-the-dax-queries-for-sql-server-analysis-services