Benefits of DAX with examples

A popular query language from Microsoft is DAX. Unlike SQL dialects, DAX allows analysts to focus on solving business intelligence problems instead of dealing with routine technical issues (such as performance issues).

Of course, DAX is not a panacea for solving any problems, but, frankly speaking, getting acquainted with this functional language can be a kind of discovery that creating a single language for all SQL dialects is generally “doable”, and it supports almost all existing databases of many types (for example, relational, columnar), and also provides high query performance.

This article discusses the benefits of DAX using specific examples, so if you are interested in Business Intelligence on DAX – welcome 🙂

All queries from the article are available at https://dax.do.

Analysis of sales of low-cost products by product category and customer

Let's look at an example on the star diagram for data on sales of products Product by customers Customer. We consider that products with a price below 100 are inexpensive. We will get data on sales of inexpensive products, grouped by the product category Product'[Category]to the client 'Customer'[Customer Name]and we will also calculate the maximum price below 100 for each group.

Of course, the example may look somewhat synthetic, but it is nevertheless close to real tasks. What does DAX look like for solving this problem? By analogy with SQL dialects, do you need to remember the table keys and conditions for joining them, list some fields that need to be selected and others by which to group, use window functions or subqueries? No, this DAX has only 10 lines (https://dax.do/VIRRo1ssjbUvWT).

EVALUATE
SUMMARIZECOLUMNS (
    'Product'[Category],
    'Customer'[Customer Name],
    "Max Price Below 100",
        CALCULATE (
            MAX ( Sales[Unit Price] ),
            FILTER ( Sales, Sales[Unit Price] < 100 )
        )
)

The data scheme is set in the UI designer and is not described in each request. Within SUMMARIZECOLUMNS, we immediately select the 'Product' fields[Category] and 'Customer'[Customer Name]and group by them. In CALCULATE ( MAX ( … ) ) we calculate the maximum values ​​for each group 'Product'[Category] and 'Customer'[Customer Name]and finally in FILTER ( Sales, Sales[Unit Price] < 100) we choose inexpensive products, cheaper than 100.

Accumulated sales by month and year

Let's calculate the accumulated amount of sales Sales[Quantity] from January 1 of each year to the current date in terms of months and years.

It would seem that the solution should be voluminous, recalling the SQL dialects. But no, only 6 lines, since DAX has Time Intelligence and the corresponding TOTALYTD function (https://dax.do/kPL7EwICLCfYsX).

EVALUATE
SUMMARIZECOLUMNS (
    'Date'[Calendar Year],
    'Date'[Calendar Year Month],
    "Sales Quantity YTD", TOTALYTD ( SUM ( 'Sales'[Quantity] ), 'Date'[Date] )
)

Measures and Variables in DAX

To illustrate the flexibility and conciseness of DAX, consider measures and variables. Measures are defined within tables, syntactically referenced as columns, and measures are calculated at query execution time. Variables, on the other hand, make a DAX query easier to understand.

Let's calculate the total sales for the last days of each year. In terms of SQL dialects, this looks like a fairly large task, but in DAX it can be solved in 20 lines of code with 1 measure 'Sales'[Total Quantity Last Day] and one variable TotalQuantityLastDay (in the example https://dax.do/DAQtVSbaAufR1s new measure 'Sales' not created[Total Quantity Last Day] and the existing measure 'Sales' is used[Total Quantity]).

Total Quantity Last Day :=
    CALCULATE (
        SUM ( 'Sales'[Quantity] ),
        FILTER ( 'Sales', 'Sales'[Order Date] = MAX ( 'Sales'[Order Date] ) )
    )

DEFINE
    VAR TotalQuantityLastDay =
        SUMMARIZECOLUMNS (
            'Date'[Calendar Year],
            "Total Quantity Last Day", 'Sales'[Total Quantity Last Day]
        )

EVALUATE
TOPN (
    1000,
    TotalQuantityLastDay,
    'Date'[Calendar Year], ASC,
    [Total Quantity Last Day], ASC
)

In conclusion, I would like to note that there is not only the DAX implementation from Microsoft, but also alternative ones, for example, Visiology. I hope these examples helped to attract interest in DAX or to open new horizons of BI analysis 🙂