Microsoft SQL Server 2012 Analysis Services: DAX Basics

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.