# Microsoft SQL Server 2012 Analysis Services: DAX Basics

- By Alberto Ferrari, Marco Russo, Chris Webb
- 7/15/2012

## Using Basic DAX Functions

Now that you have seen the basics of DAX, it is useful to check your knowledge of developing a sample reporting system. With the limited knowledge you have so far, you cannot develop a very complex solution. Nevertheless, even with your basic set of functions, you can already build something interesting.

Start loading some tables from
*AdventureWorksDW* into a new Tabular project. You
are interested in DimDate, DimProduct, DimProductCategory,
DimProductSubcategory, and FactResellerSales. The resulting data model
is shown in Figure 4-13.

**Figure 4-13** The Diagram View shows the structure of the demo data
model.

To test your new knowledge of the DAX language, use this data model to solve some reporting problems.

First, count the number of products and enable the user to slice
them with category and subcategory as long as it is with any of the
DimProduct columns. It is clear that you cannot rely on calculated
columns to perform this task; you need a measure that just counts the
number of products, which we call *NumOfProducts*.
The code is the following.

NumOfProducts := COUNTROWS (DimProduct)

Although this measure seems very easy to author, it has an issue.
Because DimProduct is a slowly changing dimension of type 2 (that is, it
can store different versions of the same product to track changes), the
same product might appear several times in the table, and you should
count it only once. This is a common scenario and can be easily solved
by counting the number of distinct values of the natural key
of the table. The natural key of DimProduct is the
*ProductAlternateKey* column. Thus, the correct
formula to count the number of products is as follows.

NumOfProducts := DISTINCTCOUNT (DimProduct[ProductAlternateKey])

You can see in Figure 4-14 that, although the number of rows in the table is 606, the number of products is 504. This number correctly takes into account different versions of the same product, counting them only once.

**Figure 4-14** *DISTINCTCOUNT* is a useful and common function for
counting.

This measure is already very useful and, when browsed through Excel, slicing by category and subcategory produces a report like the one shown in Figure 4-15.

**Figure 4-15** This is a sample report using *NumOfProducts*.

In this report, the last two rows are blank because there are products without a category and subcategory. After investigating the data, you discover that many of these uncategorized products are nuts, whereas other products are of no interest. Thus, you decide to override the category and subcategory columns with two new columns by following this pattern:

If the category is not empty, then display the category.

If the category is empty and the product name contains the word “nut,” show “Nuts” for the category and “Nuts” for the subcategory.

Otherwise, show “Other” in both category and subcategory.

Because you must use these values to slice data, this time you cannot use
measures; you must author some calculated columns. Put these two
calculated columns in the DimProduct table and call them
*ProductCategory* and
*ProductSubcategory*.

ProductSubcategory = IF( ISBLANK( DimProduct[ProductSubcategoryKey] ), IF( ISERROR( FIND( "Nut", DimProduct[EnglishProductName] ) ), "Other", "Nut" ), RELATED( DimProductSubcategory[EnglishProductSubcategoryName] ) )

This formula is interesting because it uses several of the newly
learned functions. The first *IF* checks whether the
*ProductSubcategoryKey* is empty and, if so, it
searches for the word “nut” inside the product name.
*FIND*, in the case of no match, returns an error,
and this is why you must surround it with the
*ISERROR* function, which intercepts the error and
enables you to take care of it as if it is a correct situation (which,
in this specific scenario, is correct). If *FIND*
returns an error, the result is “Other”; otherwise, the formula computes
the subcategory name from the DimProductSubcategory by using the
*RELATED* function.

With this calculated column, you have solved the issue with the
*ProductSubcategory*. The same code, by replacing
*ProductSubcategory* with
*ProductCategory*, yields to the second calculated
column, which makes the same operation with the category.

ProductCategory = IF( ISBLANK( DimProduct[ProductSubcategoryKey] ), IF( ISERROR( FIND( "Nut", DimProduct[EnglishProductName] ) ), "Other", "Nut" ),RELATED( DimProductCategory[EnglishProductCategoryName] ))

Note that you still must check for the emptiness of
*ProductSubcategoryKey* because this is the only
available column in DimProduct to test whether the product has a
category.

If you now browse this new data model with Excel and use the newly created calculated column on the rows, you get the result shown in Figure 4-16.

**Figure 4-16** You can build a report with the new product category and
subcategory, taking care of nuts.