SSAS Tabular-Part 2: Easy DAX: Getting Started with Data Analysis Expressions

Starting Your Modeling Career – with Analysis Services Tabular Models

Four-part series

Paul Turley, SolidQ Mentor & SQL Server MVP

Part 1 - Getting Started with SSAS Tabular
Part 2 - Easy DAX: Getting Started with Data Analysis Expressions
Part 3 -  Tabular Model Administration
Part 4 - Dive DAX – Solving Complex Business Problems with Data Analysis Expressions

Easy DAX: Getting Started with Data Analysis Expressions

The DAX language really serves three different purposes. Calculated columns perform row-by-row operations that are processed when data is loaded and the calculated results are persisted into the Tabular model. Measures are calculated at query time on a filtered set of rows. DAX is also a query language and can return an entire result set for reporting.   

Continuing our Tabular modeling journey, we will use the project created in the first article, “Getting Started with SSAS Tabular.” 

This article will give you a good start with DAX measures, and then we’ll resume and go deeper in the fourth article: “Deep Dive DAX – Solving Complex Business Problems with Data Analysis Expressions.”   

Simple Measures

At its heart, DAX is a very simple expression language that, much like Excel, uses functions to perform operations on values and sets of data. The most fundamental functions are simple and easy to use, and then they get more complex and powerful. The first group of DAX functions you’ll learn are aggregates that roll up a range of values into a single result. These functions include SUM, AVERAGE, COUNT, DISTINCTCOUNT, MIN and MAX. 

At the conclusion of the first article, we used the Auto Sum feature to generate some simple measures for several numeric fields in the Internet Sales and Reseller Sales tables. Let’s take a look at these. Open the Model.BIM file in the model designer and choose the Internet Sales table using the tabs at the bottom of the page. Below the Order Quantity column, you should see the auto sum measure we created. The lower grid is called the Calculation Area. Click this cell and take a look at this measure expression in the formula bar above the grid: 

Sum of Order Quantity:=SUM([Order Quantity]) 

I’m not a big fan of this “Sum of…” naming convention. I think its fine for small, simple models, but it doesn’t work very well in a variety of cases--and here is one of those cases. Take a look at the measure under the “Order Quantity” column in the Reseller Sales table. That one is named “Sum of Order Quantity 2” because the designer needed to created unique measure names and arbitrarily stuck a number at the end. Let’s fix this. For each of the measures in the Internet Sales table, replace the text “Sum of” with “Internet.” In the Reseller Sales table, replace the “Sum of” prefix with “Reseller” and remove any trailing numbers in the name. After making each change, press the Enter key and wait for it to save changes. Renaming objects is tedious work, but it’s important to get it right before creating expressions that depend on these names. 

Place the cursor in the left-most cell, top row of the calculation area and enter this expression: 

Internet Sales Count:=COUNTROWS('Internet Sales') 

Note that as you type, the code-completion feature will assist. You can use the up and down arrows on the keyboard and the Tab key to select a highlighted item. A tip displays the syntax and parameters for the function indicating the name of a table is needed to calculate a count of rows. This is a powerful feature when you learn to work with it. 

Figure 1 – DAX Expression Editor 

A caveat to the interactive expression editor is that it is, well, not perfect. In fact, it can be downright frustrating at times, and there’s no silver bullet fix for some occasional quirkiness. Sometimes it adds extra punctuation characters or won’t recognize keystrokes. The editor works well most of the time, but if you get stuck, double-check brackets, quotes and parentheses; press Escape to undo; copy and paste the script to and from Notepad; and, if necessary, close and re-open the model editor. 

Row and Filter Context 

Click the Excel icon button on the model designer toolbar and then click OK to browse the model in a PivotTable. The field list is displayed on the right when the PivotTable is selected. Check the box next to the Reseller Sales Amount measure in the Reseller Sales table and then check the Country Region field in the Geography table to show Sales totals for each country. This is pretty simple, but let’s think about what’s happening here: The expression SUM([Sales Amount]) is applied to each group of country values.  More specifically, the SUM is applied within the context of every row of the Reseller Sales table filtered by the country that appears on the PivotTable row group. This means that each aggregated row in the PivotTable may represent thousands of rows in the Reseller Sales table. 

Now, we’ll override the natural filter context and apply our own filter logic. To do this, we have to tell the query engine to ignore any groups or filters and do only what we instruct. Leave Excel open and return to the model editor. In the calculation area, move to an empty cell under the Sales Amount column and type the following text into the formula bar. Line breaks are optional and can be entered by pressing Shift and Enter. Spaces are added with the space bar (not the Tab key): 

US Reseller Sales:=CALCULATE( [Reseller Sales Amount],  
              ALL( 'Reseller Sales' ),  
              Geography[Country Region] = "United States"  
      )  

Press Enter and then use the Properties window to set the Format to Currency. Press Enter again and then move down a cell; enter this expression and then set the Format to Percent: 

Sales % of US Sales:=DIVIDE( [Reseller Sales Amount], [US Reseller Sales] ) 

The DIVIDE function performs mathematical division with a built-in safeguard for blank and zero value handling so you don’t have to worry about division by zero problems. 

Switch to Excel and click the Refresh All button on the Data ribbon. With any cell in the PivotTable selected, you’ll see the new measures added to the Reseller Sales table. Check both the US Reseller Sales and Sales % of US Sales measures to add them to the PivotTable. 

Figure 2 – Results in Excel PivotTable 

Notice that the US Reseller Sales measure doesn’t aggregate by the country on that row because we used the ALL function to override the natural filter.  By comparing the Reseller Sales Amount for the each country, we can calculate the percentage related to U.S. sales. This is fine if we hard-wire all the filters into an expression, but it breaks when we try to filter by anything else. To test this, add the Calendar Year field from the Order Date table to the top of the Columns list and you’ll see that the US Reseller Sales values aren’t filtered by year. To fix this, we can replace the ALL function with the ALLEXCEPT function like this: 

US Reseller Sales:=CALCULATE( [Reseller Sales Amount],  
              ALLEXCEPT( 'Geography', Geography[Country Region] ),  
              Geography[Country Region] = "United States"  
      ) 

The ALLEXCEPT function tells the query engine to honor all filters except for the ones we override. Any number of exceptions can be provided using optional parameters entered before the closing parentheses. This function returns a table object to the second CALCULATE function parameter and then the third parameter applies the Country Region filter. 

Save your work. In the third article, you will learn about Tabular model administration and security where you will apply some of your DAX skills. In the fourth article we’ll explore more complex business applications for DAX calculations. 

continue to part 3

Resources

Download sample project for this article

These are outstanding online resources and books I recommend. Some apply specifically to Power Pivot, but these skills transfer easily to SSAS Tabular models. 

DAX Patterns; Alberto Ferrari, Marco Russo 
www.DaxPatterns.com

Microsoft Tabular Modeling Cookbook, Paul te Braak 
http://www.amazon.com/Microsoft-Tabular-Modeling-Cookbook-Braak-ebook/dp/B00HK3VP4K/ref=sr_1_1?s=books&ie=UTF8&qid=1420856375&sr=1-1

DAX Formulas for PowerPivot, Rob Collie 
http://www.powerpivotpro.com/the-book 

Dashboarding and Reporting with Power Pivot and Excel, Kasper de Jonge 
http://www.amazon.com/Dashboarding-Reporting-Power-Pivot-Excel/dp/1615470271