### Fundamentals of DAX

First, a little background. DAX stands for**D**ata

**A**nalysis e

**X**pressions language. This PowerPivot syntax is similar to Excel expressions, but also allows users to handle data stored in different tables in more advanced calculations. Therefore, while Excel uses table coordinates such as A1, or C3, DAX uses tables and columns as coordinates. Post 1.6 touched upon the notion of row and column oriented databases. Clearly, DAX is column oriented, and can pull a value for a column in a single row, the entire table, or some subset in between.

We will cover DAX syntax, data types handled by DAX, basic operators, and how to refer to tables and columns in PowerPivot in this post. Here we go!

### DAX Syntax

Let us start by an example. Opening the PowerPivot window, we observe the**Sales**table with columns

**SalesAmount**and

**TotalProductCost**towards the end.

Fig. 1: Sales Table in PowerPivot |

**TotalProductCost**from the

**SalesAmount**gives us the margin. Type the following formula for the

**GrossMargin**:

=Sales[SalesAmount]-Sales[TotalProductCost]

Fig. 2: GrossMargin Column Added |

### DAX Data Types

DAX can handle numerical values, such as:- Integer
- Real
- Currency
- Date (or datetime)
- True/False (Boolean)
- String
- BLOB (Binary Large OBject)

PowerPivot has a great type-handling system; we can use DAX expressions and the resulting type is based on the type of terms used in the expressions. So if a term in the expression is a date, then the result would be a date, if an integer, then the result would be an integer. This is called operator overloading. For example, we can add a week to the

**Date**column by adding seven to create a

**DatePlusOneWeek**column.

=Sales[Date] + 7

Fig. 3: DatePlusOneWeek Column Calculation |

**Date**column and returns the Date a week later, in

**DatePlusOneWeek**. June 1, 2008 was transformed into June 8, 2008, as a date numeric type.

PowerPivot will automatically convert numbers into strings and in reverse when the operator requires it. So when we have the expression

= 5 + 4

and replace the operator with & to concatenate strings, the 5 and 4 will be converted into strings. The result of

= 5 & 4

is "54" (the " " indicates a string.) In reverse, when we have strings "5" and "4" being added together:

= "5" + "4"

we get 9. Using the operator to determine the type of the resulting value is convenient, but we will encounter handling errors in later posts.

__Considerations of Specific Data types:__

__Date__

PowerPivot stores

**datetime**as a floating point number. In fact, the floating point number corresponds to the number of days starting from December 30, 1899, and the decimal represents the fraction of the day. The seconds, minutes, and hours in a day are converted into decimal fractions. If we evaluate this idea by:

= NOW() + 1

we would have an expression which adds one to the floating point. Thus it adds one day to today and we get essentially the date for tomorrow (with the same hours, minutes, and seconds.)

__True and False__

True and False represent logical conditions, and is usually called a Boolean data type. For example, the expression

=Sales[

**TotalProductCost**] >= Sales[

**SalesAmount**]

would return True if the

**TotalProductCost**of the row was greater or equal to the

**SalesAmount**, and False if the it was less.

### Dax Operators

Figure 4 below shows the different DAX operators and examples. We have seen many before.

Fig. 4: DAX Operator Types |

### DAX Values

The values used in Figure 4, such as "USA" or 0, are called

__literals__and is straightforward. However, when referencing column names in the format:
'Table'[Column]

we usually can omit the quotation marks if the table name has no special characters or spaces to:

Table[Column]

We also can use the column name by itself, which is always enclosed in square brackets:

[Column]

and the expression will find the referenced column name in the current table.

For the sake of readability, and to prevent mistakes, use the table name in conjunction with the column name.

### Note on IntelliSense:

When we type into the formula box in PowerPivot, a helpful window appears, listing the names of all the function names and references. For example, if we type in

=[

to begin a column name, a listing of all the column names will appear.

Fig. 5: IntelliSense Displaying the Possible Column Names |

We will continue to explore DAX in the next post, P3.2.

Thanks for reading,

Wayne

## No comments:

## Post a Comment