Introduction to Using DAX in PowerPivot


No, I’m talking about the stock market index in the German market.  DAX stands for Data Analysis Expressions and is used in PowerPivot to create calculated columns. DAX has a  lot of similarities to Excel formulas and as we go through the discussion this week and at least next week, you will see a considerable overlap in the way you can use DAX.  However, there are also a lot of differences.  In fact, there are a lot of very interesting additions to DAX when we look at functions next time.

But first you need to understand the basics.  No, we are not going to write a ‘Hello World’ program, but we will look at syntax because you cannot create a valid DAX expression unless you understand the syntax.  So let’s get started.

Just like any other expression in Excel, DAX formulas always begin with an equal sign.  After the equal sign you will find a combination of common expressions, references to columns and table, and of course DAX functions.  Expressions must resolve to a scalar value.  Therefore an expression could be as simple as a reference to another column, even another column in another table in your PowerPivot database.  So how do you reference columns and tables.

Each table you add to your PowerPivot database must have a name.  Furthermore, that name must be unique within the database.  For now, let just say that table names must consist of alphanumeric characters (I’ll list the restrictions later).  You can even have spaces within the table name.  However, my personal preference is not to include spaces but rather to use Pascal case to combine multiple words.  For example, some common table names include:  ‘Sales’, ‘Employees’, ‘GeographicAreas’, and ‘StudentTestScores’.  Notice that I’ve places each table name inside a set of apostrophes.  For single word names with no spaces, this is not necessary, but it is still a good habit to get into.

Within each table, each column must have a name that is unique within that table.  However, you can use the same column name in different tables of the database.  Again, my best practice recommendation is to only use the same name for a column in two or more tables if that column is used as a linking column when defining relations between the tables.  Column names can also consist of any alphanumeric column including spaces.  As with table names, my preference is to eliminate spaces and use Pascal case to create my column names.  When used in a DAX formula, column names are typically enclosed in square brackets such as: [TotalProfit], [Tax], [SATMathScore], and [LastName].

Putting both of these naming conventions together, you may have column references such as: ‘Sales’[TotalRevenue], ‘Employees’[LastName], and ‘StudentTestScores’[SATMathScore].  You don’t necessarily need to use fully qualified names such as these (having both the table and column name).  If the column is unique or if you are referencing a column name in the current table where you are adding the formula, you can use just the column name.

Table and Column Naming Rules

  • Leading or Trailing spaces are not allowed unless enclosing the name in quotes or brackets.
  • Control characters are not allowed
  • Special characters such as the following are not allowed: . , ; ‘ : ^ / \ + * | ? & % $ ! + { } [ ] ( ) < >

Referencing a single column or table by itself really a trivial case and the real interest is in combining columns together and using them in functions.  The easiest way to create an simple expression is with the basic operators shown in the following table:

Operators

Arithmetic:

  • + Addition
  • – Subtraction
  • * Multiplication
  • / Division
  • ^ Exponentiation

Text Concatentation

  • & Concatenate two text values

Logical Operators

  • && And
  • || Or

Comparison Operators

  • = equal
  • > Greater than
  • < Less than
  • >= Greater than or equal to
  • <= Less than or equal to
  • <> Not equal to

Using these operators, you can create a simple expression to calculate profits based on sales and costs such as: ‘Sales’[Revenue] – ‘Sales’[Costs]. 

As with most expressions, calculations proceed from left to right with exponentiation performed first, then multiplication and division and then addition and subtractions.  So if you wanted to calculate your profit as a percentage of sales, you might enter: ‘Sales’[Revenue] – ‘Sales’[Costs] / ‘Sales’[Revenue] * 100.  However, you would not get the results you expected because the system would first divide Costs by Revenue, multiple by 100 and then subtract it from revenue.  What you need is to apply standard precedence characters, the parenthesis as in: (‘Sales’[Revenue] – ‘Sales’[Costs]) / ‘Sales’[Revenue] * 100.  Now costs are subtracted from revenues first with the result divided by revenue and then multiplied by 100.

As you may have guessed DAX expressions recognize specific data types.  Most of these data types are similar those found in Excel.  PowerPivot does its best to use these data types when you bring data in from other sources converting to data types it recognizes automatically.  These data types include:

Boolean True and False
Currency -922,337,203,685,477.5808 to 922,337,203,685,477.4807
Date Valid dates after 3/1/1900
Decimal 64-bit with a range of -1.79E+308 to -2.23E-308, 0, 2.23E-308 to 1.79E+308
N/A Blank, similar to SQL Null.  Test for blank with the function ISBLANK()
Text max length of 268,435,456 unicode characters
Whole Numbers 64-bit with values from (-2^63) to 2^63-1)

 Most of the time, DAX will implicitly convert values based on the needs of the current function.  For example, using the concatenation connector between two integer values results in a string as in: 12 & 34 = “1234”.  Similarly, it will automatically try to promote one or more values in an expression until the expression can be performed as in: “12” + 3 = 15.  Unfortunately, you do not have the ability to explicitly perform a data type conversion at this time.  So in the last example, you cannot include the expression in a type conversion function to return a string of “15”.

In the next entry in this series, I’ll talk about the functions available in DAX and give you links to on-line resources that define the functions.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s