By: Siddharth Mehta
Overview
DAX is not a query language technically, even though in SSMS you can find options to open a new MDX / DMX / XMLA query. You will not find options to open a DAX query, as DAX is an expression language and not a query language although you can use the MDX query pane to evaluate DAX expressions. In this chapter we will learn about certain basics rules and conventions of DAX formulas. We will also look at browsing / selecting data from a tabular model and understand the behavior of formulas based on context.
Explanation
- DAX enables tabular model authors to define custom calculations in both model tables, as part of calculated columns, and as measures associated with tables. When a calculated column contains a valid DAX formula, values are calculated for each row as soon as the formula is entered and values are then stored in the database.
- In DAX, each formula must begin with an equal sign with a function name or expression.
- A calculated column is a column that is added to an existing table using a DAX formula that defines the column values.
- A Measure is a dynamic DAX formula where the results change depends on context.
- A DAX function always references a complete column or a table. If you want to use only particular values from a table or column, you can add filters to the formula.
- DAX provides functions that let you use the current row value or a related value as a kind of parameter, to perform calculations that vary by context. These kind of functions are used to customize calculations on a row-by-row basis.
- DAX includes many functions that return a table rather than a value.
- DAX contains three major types of context, based on how it evaluates
the formula - Row Context, Query Context, and Filter Context.
- Row context can be thought of as the current row. If you create a formula in a calculated column, the row context for that formula can include the values from all columns in the current row.
- Query context refers to the subset of data that is implicitly retrieved for a formula. When a user places a measure or other value field into a PivotTable or into a report based on a tabular model, the engine examines the row and column headers, slicers, and report filters to determine the context. The necessary queries are run against the data source to get the correct subset of data, make the calculations defined by the formula, and then populate each cell in the PivotTable or report. The set of data that is retrieved is the query context for each cell.
- Filter context is added when you specify filter constraints on the set of values allowed in a column or table, by using arguments to a formula. Filter context applies on top of other contexts, such as row context or query context.
Calculated Columns
Open Model.bim in the tabular model that we setup in the last chapter. In the below screenshot, you can see the DAX formula in the formula pane. This formula is for the calculated column Gross Profit, which uses two base columns Sales Amount and Total Product Cost. This formula can access values from any columns in the same row, which is evident from the values of the Gross Profit column. You can create any number of calculated columns using DAX formula in this manner.
Measures
In the below screenshot, the DAX formula shows the convention to create a measure. You can just enter the name of the measure you want to create with a := sign and assign the DAX formula for the measure. Here the Total Sales measure is the addition of two other measures - Reseller Total Sales and Internet Total Sales.
Reports
If we analyze this tabular model in Excel, the report would look similar to the below. In this report we have used Internet Total Sales, Reseller Total Sales, and Total Sales measures on the columns and Geography on the rows. When these fields are selected, on every selection the corresponding query is formulated and sent to the database. When this query is processed, the DAX formula is evaluated and corresponding values, depending on the context, are returned back to the client application.
Perspectives
In a tabular model, all the tables and fields are organized by perspectives, which is a way to create views on top of the base schema of the model. As you can see in the below screenshot, all the tables and fields are part of one or more perspectives based on the functional area.
Additional Information
- Try to create a few calculated fields and measures in the data model using base columns as well as operators and functions explained in the previous chapter. Process and deploy the same and analyze how you can use the same using Excel.
