DAX Basics
Data Analysis Expressions (DAX) is a formula expression language used in Analysis Services, Power BI, and Power Pivot in Excel. It's designed for working with data stored in tabular data models.
What is DAX?
DAX combines functions from Excel and SQL, along with new, powerful functions specifically designed for tabular models. It allows you to create calculated columns and measures that derive new information from existing data.
Key Concepts
- Measures: Calculations that are dynamically computed based on user interaction (e.g., filtering and slicing). They are typically used for aggregations like sums, averages, or complex business logic.
- Calculated Columns: Columns added to a table whose values are computed row by row based on a DAX formula. Their values are stored in the model.
- Tables: Collections of related data, similar to tables in a relational database.
- Relationships: Connections between tables that enable calculations to flow between them.
- Filter Context: The set of filters applied to the data model at any given time, which affects how DAX formulas are evaluated.
- Row Context: The current row being processed in a table during the evaluation of a DAX expression, typically within calculated columns or iterators.
Basic DAX Functions
DAX offers a rich set of functions. Here are a few fundamental ones:
Aggregation Functions
These functions perform calculations over a column or a set of columns.
SUM(: Returns the sum of all numbers in a column.) AVERAGE(: Returns the average of numbers in a column.) MIN(: Returns the smallest value in a column.) MAX(: Returns the largest value in a column.) COUNT(: Counts the number of non-blank values in a column.) DISTINCTCOUNT(: Counts the number of unique values in a column.)
Aggregate Example
To create a measure that calculates the total sales amount:
Total Sales = SUM( 'Sales'[SalesAmount] )Filter Functions
These functions modify the filter context, allowing for more complex calculations.
CALCULATE(: Evaluates an expression in a modified filter context. This is one of the most powerful functions in DAX.[, [, [, ... ] ] ] ) FILTER(,
): Returns a table that has been filtered. ALL(: Removes all filters from the specified table or column.) CALCULATE Example
To calculate sales for a specific year (e.g., 2023):
Sales 2023 = CALCULATE( [Total Sales], 'Date'[Year] = 2023 )Note: The table and column names used in the examples (e.g.,'Sales'[SalesAmount],'Date'[Year]) are placeholders. Replace them with the actual names from your data model.Logical Functions
These functions perform logical tests and return results based on those tests.
IF(: Checks a condition and returns one value if TRUE and another if FALSE., , ) AND(: Returns TRUE if all arguments are TRUE., ) OR(: Returns TRUE if any argument is TRUE., )
IF Example
Create a calculated column in the 'Products' table to categorize products based on their price:
Price Category = IF( 'Products'[Price] > 100, "High", IF( 'Products'[Price] > 50, "Medium", "Low" ) )Next Steps
Understanding these basic concepts and functions is crucial for building effective DAX models. Further exploration into time intelligence functions, iterators, and advanced filter context manipulation will unlock the full power of DAX for your business intelligence solutions.