[00:00:00.00]
When we introduced the BI Semantic Model, we talked about its architecture having three fundamental components:
1. The Data Model
2. Business Logic & Queries
3. Data Access
For a Tabular BI Semantic Model that we implement either with PowerPivot or Analysis Services, the expression language that we use is called DAX. DAX is an acronym for Data Analysis Expressions, and it’s a set of Excel-like expressions and functions and constructs that are purpose-built and tuned for the kind of multidimensional analysis that we’re doing in these kinds of solutions.
[00:00:41.23]
Much of what we’ll use DAX for in our semantic model is implementing calculations. So we might want to ask ourselves, “Why do we want to put calculations in this semantic model in the first place?” There are a few reasons why we want to do this:
First, simplifying the end user experience. Most end-users want to use data; they don’t want to define it and develop calculations.
Next is centralizing calculations. Some calculations are difficult to implement or could be implemented in more than one way. By centralizing certain calculations, we can make sure that they are implemented properly in every end-user report. If we need to change anything about a calculation that’s implemented centrally then that change becomes propagated wherever that calculation is used.
[00:01:27.28]
Next is advanced data analysis. DAX is a language that’s specifically designed to allow very advanced data analysis with pretty simple syntax. So it’s the right tool for the job for many of these needs.
Next is to provide a way to model data so it reflects business needs.
[00:01:45.16]
Some examples of what we mean by that:
First, we use DAX to “flatten” (denormalize) data to make it easier for end-users to use.
Creating predefined calculations. Ratios like Average Selling Price, Gross Margin %, etc. These are things that typically get implemented din reports or Excel. These are things that are very easy to put into semantic models so those end-user reports can just use the predefined calculation rather than knowing what the inputs are for every calculation.
[00:02:25.22]
Ranking, running totals, percent of whole calculations can be done in Excel are things we can put into the mode so users can spend less time implementing these calculations and more time using them.
[00:02:45.00]
Finally, time-based calculations can be very challenging sometimes. Calculations like ending inventory and rolling averages, for example. These are complicated calculations, but the BI Semantic model with DAX makes many of these calculations easy to implement. And again they are implemented in a centralized way so they can be implemented once and used in a number of different applications.
[00:03:15.10]
Where will we typically write DAX? There are three primary places:
1. In the PowerPivot formula bar. This is where we typically make calculated columns. Calculated columns extend additional columns onto tables we already have. A good example would be denormalizing a table so the Product Category name is stored in the Product table rather than requiring a table join each time it’s used.
[00:03:46.26]
2 In the PowerPivot ribbon in Excel. As we browse our model in an Excel Pivot Table, we can click the measure button to create new measures and add them to the model right from the Pivot Table.
3. An area that developers will use is the SQL Management Studio (SSMS) tool to send queries to the Tabular database to get results. End-users will not use this interface very often.
[00:04:26.29]
Let’s look at basic DAX syntax before we even try to write any expressions. Many DAX expressions look just like Excel. Here is a simple IF() function, which looks just like Excel. This simple statement returns a string depending on a condition.
Excels expression is almost identical to this one.
DAX expressions often will reference tables and columns. This next expression references a table and column. The quotes are optional when a table name doesn’t have a space in the name. This syntax is how we refer to single columns within a table.
Operators (yellow). In this example were multiplying a constant. Later we’ll look at the operators DAX supports. Like any languages it has a typical set of mathematical and comparison operators.
[00:05:49.14]
DAX very often calls functions. Her we’re making a function call using the SUM() function to sum the orders amount column. Then we’re using an operator to multiply the sum by a constant 1.25.
There are any functions in DAX, and many of those are similar to Excel functions.
[00:06:11.21]
As you learn DAX you have to get used to functions returning values in some cases, while in other cases functions return tables. We may have functions that accept tables as parameters, and use other functions to return tables for those parameters. Be cognizant of what the return from functions will be, and whether that return is what you need depending on how you use the function’s return value(s).
[00:06:27.22]
Finally, here is a somewhat intimidating list of DAX Functions and operators. If you scan through these, you’ll see things like DATE, HOUR, SECOND, etc. Many of these are obvious in what they do.
Many time intelligence functions are available, such as DatesMTD, which gives us a set of dates from the beginning of the month until the current date.
Statistical functions are available to perform common stat functions like Average.
[00:07:27.13]
This is the entire list, and over the course of the lesson series we’ll look at many of these.
