Introduction To DAX: Data Analysis Expressions Formulas
What is DAX?
- Functional language of Power Pivot in Excel, the Tabular Model in SQL Analysis Services and Power BI
- A collection of functions, operators and constants that can be used in formula or expressions to calculate and return one or more values
- DAX formulas can help solve real-world problems BI problems through the creation of calculated columns and measures (also known as calculated fields)
- Enables powerful data analysis across relational data, such as calculating percentage growth across product categories and for different ranges of dates
- Understanding DAX involves three fundamental concepts: Syntax, Functions and Context
DAX Types
o Numeric
- Integer (64 bit)
- Decimal (floating point)
- Currency (money)
- Date (Date/Time)
- TRUE / FALSE (Boolean)
o Other
- String
- Binary Objects
DAX Syntax
Total Sales = SUM(Sales[SalesAmount])
- Total Sales is the name of the measure
- The equals sign operator (=) indicates the beginning of the formula. When calculated, it will return a result
- The DAX function SUM adds up all the numbers in the table column being passed in the arguments
- The parenthesis () surround an expression containing one or more arguments. All functions require at least one argument. An argument passes a value to a function
- In this example, Sales is the name of the table being referenced by the function
- [SalesAmount] is the name of the column being referenced in the Sales table. With this argument, the SUM function knows which column to use to aggregate a SUM.
DAX Functions
- Predefined formulas that perform calculations using specific values, passed as arguments, in a particular order or structure
- Arguments can be in the form of functions, formulas, expressions, column references, numbers, text, logical values or constants
- DAX includes the following function categories: Date/Time, Time Intelligence, Information, Logical, Mathematical, Statistical, Text, Parent/Child and Others
- They will appear similar to functions found in Excel formulas, but have their own unique characteristics, including being able to perform calculations that vary by context, the ability to return a table rather than a value and the ability to work across relationships in the data model
DAX Context
- One of the most important concepts to understand when working with DAX
- Two types of contexts in DAX:
- Row context
- Most easily thought of as the current row
- Applies when a formula has a function that applies filters to identify a single row in a table
- Filter context
- Row selection
- Column selection
- Slicer selection
- Filter selection
Filter Context
Store Sales = CALCULATE([Total Sales], Channel[ChannelName] = “Store”)
- Store Sales is the name of the measure
- The equals sign operator (=) indicates the beginning of the formula. When calculated, it will return a result
- The DAX function CALCULATE evaluates an expression, as an argument, in a context that is modified by the specified filters
- The parenthesis () surround an expression containing one or more arguments
- In this example, [Total Sales] is another measure in the same table as an expression. The total sales measure has the formula =SUM(Sales[SalesAmount])
- A comma (,) separates the first expression argument from the filter argument
- The fully qualified referenced column Channel[ChannelName] provides the row context. Each row in this column specifies a channel: Store, Online, etc.
- The particular value, Store, is the filter context
- This formula ensures that only sales values defined by the Total Sales measure are calculated, only for rows in the Channel[ChannelName) column with a value of “Store“, as a filter
- Being able to define filter context within a formula offers very powerful capabilities
