Today I started learning about DAX basics and how it works on Power Pivot. Here is a basic and simple concept about it.
Data Analysis Expressions (DAX) is a collection of functions, operators, and constants that can be used in a formula, or expression, to calculate and return one or more values. Stated more simply, DAX helps you create new information from data already in your model. If you are already familiar with creating formulas in Microsoft Excel it’ll becomes easier.
Let’s look at a simple DAX formula for a measure.
- The measure name Total Sales.
- The equals sign operator (=) indicates the beginning of the formula. When calculated, it will return a result.
- The DAX function SUM adds up all of the numbers in the Sales[SalesAmount] column. You’ll learn more about functions later.
- Parenthesis () surround an expression containing one or more arguments. All functions require at least one argument. An argument passes a value to a function.
- The referenced table Sales.
- The referenced column [SalesAmount] in the Sales table. With this argument, the SUM function knows on which column to aggregate a SUM.
DAX includes the following categories of functions: Date and Time, Information, Logical, Mathematical, Statistical, Text, and Time Intelligence Functions. If you are familiar with functions in Excel formulas, many of the functions in DAX will appear similar to you; however, DAX functions are unique in the following ways:
- 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.
- If you need to customize calculations on a row-by-row basis, DAX provides functions that let you use the current row value or a related value as a kind of argument, to perform calculations that vary by context. You will learn more about context later.
- DAX includes many functions that return a table rather than a value. The table is not displayed, but is used to provide input to other functions. For example, you can retrieve a table and then count the distinct values in it, or calculate dynamic sums across filtered tables or columns.
- DAX includes a variety of time intelligence functions. These functions let you define or select date ranges, and perform dynamic calculations based on them. For example, you can compare sums across parallel periods.
DAX Basics – Practice
We are learning about DAX basics so we need to practice and have a better understand of how to do it, I got the Power Pivot file of the last episode and tried to do some commands.
First one I did a SUM of sales based in the “Total” column:
Then I did an IF command that verifies if the sum of the sales is more than $3.000,00, if true, it returns an text: “Good”, if not, it returns: “Bad”.
Are you learning about dax basics now? Let’s learn more in the next episodes!