Analyzing and Visualizing Data with Excel——A Little Bit More About DAX

柔情只为你懂 2022-10-02 05:44 33阅读 0赞

### A Little Bit More About DAX ###

**NOTE:** any references to tables or field names on this page are for example only – they are not actual tables or fields used within this course. Portions of this lesson were taken from the Microsoft Office Support article QuickStart: Learn DAX Basics in 30 Minutes. For more details visit the website: [https://support.office.com/en-us/article/QuickStart-Learn-DAX-Basics-in-30-Minutes-51744643-c2a5-436a-bdf6-c895762bec1a][https_support.office.com_en-us_article_QuickStart-Learn-DAX-Basics-in-30-Minutes-51744643-c2a5-436a-bdf6-c895762bec1a]

## What is DAX? ##

Data Analysis Expressions (DAX) is a collection of functions, operators, and constants that can be used in a formula, or expression, to calculate and return one or more values. Stated more simply, DAX helps you create new information from data already in your model.

## Why is DAX so important? ##

It’s easy to create a workbook and import some data into it. You can even create pivot tables or pivot charts that display important information without using any DAX formulas. But, what if you need to analyze critical sales data across several product categories and for different date ranges? Or, you need combine important inventory data from several tables in different data sources? DAX formulas provide this capability and many other important capabilities as well. Learning how to create effective DAX formulas will help you get the most out of your data. When you get the information you need, you can begin to solve real business problems that affect your bottom line. This is Business Intelligence, and DAX will help you get there.

You might already be familiar with creating formulas in Microsoft Excel. That knowledge will be helpful in understanding DAX, but even if you have no experience with Excel formulas, the concepts described here will help you get started creating DAX formulas and solving real-world BI problems right away.

We are going to focus specifically on understanding DAX formulas used in calculations. You should already be familiar with the Excel data model. You should also be familiar with fundamental concepts of both [calculated columns][] and [calculated fields][] (also known as measures), both of which are described in the lectures.

## Syntax ##

Let’s take a look at DAX formula syntax. Syntax includes the various elements that make up a formula, or more simply, how the formula is written.

For example, let's assume we have a simple DAX formula used to create new data (named Margin) for each row in a calculated column, defined as follows: Margin:=\[SalesAmount\]-\[TotalCost\]. (formula text colors are for illustrative purposes only)

![Caclulated column formula][]

This formula’s syntax includes the following elements:

A. The equals sign operator (=) indicates the beginning of the formula, and when this formula is calculated it will return a result or value. All formulas that calculate a value will begin with an equals sign.  
B. The referenced column \[SalesAmount\] contains the values we want to subtract from. A column reference in a formula is always surrounded by brackets \[\]. Unlike Excel formulas which reference a cell, a DAX formula always references a column.  
C. The subtraction (-) mathematical operator.  
D. The referenced column \[TotalCost\] contains the values we want to subtract from values in the \[SalesAmount\] column.

When trying to understand how to read a DAX formula, it is often helpful to break down each of the elements into a language you think and speak every day. For example, you can read this formula as:

*In this* *table, for each row in the Margin calculated column, calculate (=) a value by subtracting (-) values in the \[* *TotalCost* *\] column from values in the \[* *SalesAmount* *\] column.*

Let’s take a look at another example. Let's assume a DAX formula used to create a calculated field on a FactSales table, defined as follows: Sum of Sales Amount:=SUM(FactSales\[SalesAmount\])). (formula text colors are for illustrative purposes only)

![Calculated column formula][]

This formula includes the following syntax elements:

A. The calculated field name Sum of Sales Amount. Formulas for calculated fields can include the calculated field name, followed by a colon, followed by the calculation formula.  
B. The equals sign operator (=) indicates the beginning of the calculation formula. When calculated, it will return a result.  
C. The function SUM adds up all of the numbers in the \[SalesAmount\] column.  
D. Parenthesis () surround one or more arguments. All functions require at least one argument. An argument passes a value to a function.  
E. The referenced table FactSales.  
F. The referenced column \[SalesAmount\] in the FactSales table. With this argument, the SUM function knows on which column to aggregate a SUM.

You can read this formula as:

*For the*  *calculated field*  *named Sum of Sales Amount, calculate (=) the SUM of values in the \[* *SalesAmount* *\] column in the*  *FactSales*  *table.*

When this calculated field is placed into the Values drop zone in a pivot table Field List, it calculates and returns values defined by each cell in the pivot table.

Notice there are a few things different about this formula compared to the formula we used for the Margin calculated column. In particular, we introduced a function, SUM. Functions are pre-written formulas that make it easier to do complex calculations and manipulations with numbers, dates, time, text, and more. You will learn more about DAX functions later.

Unlike the Margin calculated column earlier, you see the column \[SalesAmount\] was preceded by the table FactSales in which the column belongs. This is known as a fully qualified column name in that it includes the column name preceded by the table name. Columns referenced in the same table do not require the table name be included in the formula. This can make long formulas that reference many columns shorter and easier to read. However, it is good practice to always include the table name in your calculated field formulas, even when in the same table.

**NOTE:** If the name of a table contains spaces, reserved keywords, or disallowed characters, you must enclose the table name in single quotation marks. You must also enclose table names in quotation marks if the name contains any characters outside the ANSI alphanumeric character range, regardless of whether your locale supports the character set or not.

It is very important your formulas have the correct syntax. In most cases, if the syntax is not correct, a syntax error will be returned. In other cases, the syntax may be correct, but the values returned might not be what you are expecting. Power Pivot (and SQL Server Data Tools) includes IntelliSense; a feature used to create syntactically correct formulas by helping you select the correct elements.

**TIP:** Something really important to understand when typing an operator into a DAX formula is the data type in the arguments you are using. For example, if you were to type the following formula, = 1 & 2, the value returned would be a text value of “12”. This is because the ampersand (&) operator is for text concatenation. DAX interprets this formula to read: Calculate a result by taking the value 1 as text and add value 2 as text. Now, if you were to type = 1 + 2, DAX reads this formula as: Calculate a result by taking the numeric value 1 and adding the numeric value 2. The result is of course “3”, a numeric value. DAX calculates resultant values depending on the operator in the formula, not based on the data type of columns used in the argument.

For the more adventurous analyst who would like to learn more about DAX, refer to the Microsoft Developer Network website - Data Analysis Expressions (DAX) Reference: [https://msdn.microsoft.com/en-us/library/gg413422.aspx][https_msdn.microsoft.com_en-us_library_gg413422.aspx]

[https_support.office.com_en-us_article_QuickStart-Learn-DAX-Basics-in-30-Minutes-51744643-c2a5-436a-bdf6-c895762bec1a]: https://support.office.com/en-us/article/QuickStart-Learn-DAX-Basics-in-30-Minutes-51744643-c2a5-436a-bdf6-c895762bec1a
[calculated columns]: https://support.office.com/en-us/article/calculated-columns-a0eb7167-33fc-4ade-a23f-fb9217c193af
[calculated fields]: https://support.office.com/en-us/article/calculated-fields-86484821-a324-4da3-803b-82fd2e5033f4
[Caclulated column formula]: /images/20220113/3a0921820af240f18e8773c82cbcee6e.png
[Calculated column formula]: /images/20220113/d501d3a533b0469e8819a6596b593aad.png
[https_msdn.microsoft.com_en-us_library_gg413422.aspx]: https://msdn.microsoft.com/en-us/library/gg413422.aspx