What is DAX?

Data Analysis Expressions (DAX) is a formula expression language used in Analysis Services, Power BI, and Power Pivot in Excel. DAX formulas include functions, operators, and values to perform advanced calculations and queries on data in related tables and columns in tabular data models. DAX allows you to build complex calculations that can manipulate and analyze your data, enabling you to unlock deeper insights and answer more sophisticated questions.

Why DAX?

DAX is essential for anyone looking to go beyond basic reporting in Power BI. While Power BI’s drag-and-drop interface is great for creating simple visualizations, DAX empowers you to perform advanced analytics that can reveal trends, patterns, and relationships in your data that would otherwise remain hidden. Whether you’re calculating year-over-year growth, segmenting customers based on behavior, or performing time-based analysis, DAX is the tool that makes it possible.

Features of DAX

✅ Calculated Columns: DAX allows you to create new columns in your data models based on existing data. For example, you can create a calculated column that categorizes customers into age groups or calculates the profit margin for each sale.

✅ Measures: Measures are dynamic calculations that are performed on the fly when you interact with your reports. Unlike calculated columns, measures are not stored in the data model but are recalculated whenever you filter or slice your data. This makes measures incredibly powerful for aggregating data in real-time.

✅ Time Intelligence: DAX includes a rich set of time intelligence functions that allow you to analyze data across different time periods. Whether you’re calculating year-to-date sales, comparing this month’s performance to the same month last year, or tracking moving averages, DAX makes time-based analysis straightforward.

✅ Filter Context: One of the most powerful concepts in DAX is filter context. Filter context refers to the set of filters applied to data when a calculation is performed. DAX allows you to manipulate filter context using functions like CALCULATE, enabling you to create complex calculations that adapt to the data being viewed.

✅ Hierarchies and Parent-Child Functions: DAX supports hierarchies, allowing you to analyze data at different levels, such as product categories or geographic regions. Parent-child functions enable you to navigate hierarchical data structures, making it easy to perform calculations at different levels of aggregation.

✅ Performance Optimization: DAX includes functions that can optimize the performance of your calculations. For example, the SUMX function calculates the sum of an expression over a table and can be more efficient than a traditional SUM calculation in certain scenarios.

DAX Architecture

DAX operates within the VertiPaq engine, which is an in-memory columnar database engine used by Power BI, Power Pivot, and SSAS. VertiPaq compresses and stores data in memory, allowing for fast querying and calculations.

✅ Calculation Engine: The DAX calculation engine is responsible for evaluating expressions and returning results. It handles both simple calculations, like summing a column, and complex operations, like filtering data based on multiple conditions.

✅ Query Context and Filter Context: DAX operates within different contexts—query context and filter context. The query context is determined by the user’s interactions with a report (e.g., selecting filters or slicing data), while the filter context is the set of filters applied to the data. DAX functions can modify these contexts to produce specific results.

DAX Functions

Aggregation Functions: These functions calculate a (scalar) value such as count, sum, average, minimum, or maximum for all rows in a column or table as defined by the expression.

✅ SUM: Adds up all the values in a column. For example, SUM(Sales[TotalAmount]) calculates the total sales amount.

✅ AVERAGE: Returns the average of a column's values. For instance, AVERAGE(Sales[Quantity]) gives the average quantity sold.

✅ COUNT: Counts the number of non-blank values in a column. COUNT(Orders[OrderID]) counts the number of orders.

✅ MAX and MIN: Return the maximum and minimum values in a column, respectively.

Date and Time Functions: These functions in DAX are similar to date and time functions in Microsoft Excel. However, DAX functions are based on the datetime data types used by Microsoft SQL Server.

✅ TODAY and NOW: Return the current date and time. For example, TODAY() gives today’s date.

✅ YEAR, MONTH, DAY: Extract the year, month, or day from a date. YEAR(Orders[OrderDate]) extracts the year from an order date.

✅ DATEDIFF: Calculates the difference between two dates. DATEDIFF(Orders[OrderDate], Orders[ShipDate], DAY) returns the number of days between order and shipment.

✅ EOMONTH: Returns the last day of the month for a given date. EOMONTH(Orders[OrderDate], 0) gives the end of the order month.

✅ DATEADD: Shifts dates forward or backward by a specified number of days, months, quarters, or years.

Filter Functions: These functions help you return specific data types, look up values in related tables, and filter by related values. Lookup functions work by using tables and relationships between them. Filtering functions let you manipulate data context to create dynamic calculations.

✅ FILTER: Returns a table that contains only rows that meet a specified condition. FILTER(Sales, Sales[Quantity] > 10) returns sales where the quantity is greater than 10.

✅ ALL: Ignores any filters applied to the data. ALL(Sales[Category]) returns all categories, even if a filter is applied.

✅ CALCULATE: Modifies the filter context of a calculation. CALCULATE(SUM(Sales[TotalAmount]), Sales[Category] = "Electronics") sums the sales amount for the electronics category.

✅ RELATED: Returns a related value from another table. RELATED(Products[Category]) brings in the product category based on a relationship.

Information Functions: These functions look at a table or column provided as an argument to another function and return whether the value matches the expected type.

✅ ISBLANK: Checks if a value is blank. ISBLANK(Sales[Discount]) returns true if there is no discount applied.

✅ ISNUMBER: Verifies if a value is a number. ISNUMBER(Sales[OrderID]) ensures that order IDs are numeric.

✅ ISEMPTY: Checks if a table or column is empty. ISEMPTY(Orders) returns true if there are no orders.

Logical Functions: These functions return information about values in an expression. For example, the TRUE function lets you know whether an expression that you are evaluating returns a TRUE value.

✅ IF: Evaluates a condition and returns one value if the condition is true and another if it’s false. For example, IF(Sales[Profit] > 0, "Profitable", "Not Profitable") categorizes sales based on profitability.

✅ AND and OR: Combine multiple conditions. AND(Sales[Profit] > 0, Sales[Quantity] > 10) checks if both conditions are true.

✅ NOT: Reverses a logical value. NOT(Sales[Discount] > 0) returns true if no discount is applied.

✅ SWITCH: Evaluates an expression against a list of values and returns the corresponding result. It’s like a more powerful version of IF.

Math and Trig Functions: Mathematical functions in DAX are similar to Excel's mathematical and trigonometric functions. However, there are some differences in the numeric data types used by DAX functions.

✅ ROUND: Rounds a number to a specified number of digits. ROUND(Sales[Discount], 2) rounds discounts to two decimal places.

✅ ABS: Returns the absolute value of a number. ABS(Sales[Profit]) ensures profit values are non-negative.

✅ SQRT: Calculates the square root of a number. SQRT(Sales[Quantity]) gives the square root of the quantity sold.

✅ MOD: Returns the remainder after division. MOD(Sales[OrderID], 2) checks if an order ID is odd or even.

Parent and Child Functions: These functions help users manage data that is presented as a parent/child hierarchy in their data models.

✅ PATH: Returns a delimited text string with the IDs of all parents to a given row. This is useful for visualizing the hierarchy.

✅ PATHITEM: Retrieves a specific item from a path string. This allows you to analyze data at different levels of the hierarchy.

✅ PATHLENGTH: Returns the number of elements in a path. This can be used to calculate the depth of a hierarchy.

Relationship Functions: These functions are for managing and utilizing relationships between tables.

✅ RELATED: Returns a related value from another table, based on an existing relationship. For example, RELATED(Products[Category]) fetches the category of a product from the Products table in a related Sales table.

✅ RELATEDTABLE: Returns a table that contains all the rows related to the current row in another table. RELATEDTABLE(Sales) might return all sales associated with a specific product.

✅ USERELATIONSHIP: Temporarily uses an inactive relationship in a calculation. This is useful when you have multiple relationships between tables and want to switch to a different one for a specific calculation. For example, CALCULATE(SUM(Sales[Amount]), USERELATIONSHIP(Dates[ShipDate], Sales[Date])) calculates sales using the shipment date instead of the order date.

✅ CROSSFILTER: Modifies the cross-filtering behavior between two columns in a relationship. You can use it to control the direction of the relationship or even disable it. For example, CALCULATE(SUM(Sales[TotalAmount]), CROSSFILTER(Products[Category], Sales[Category], NONE)) disables cross-filtering between the Products and Sales tables.

Text Functions: With these functions, you can return part of a string, search for text within a string, or concatenate string values. Additional functions are for controlling the formats for dates, times, and numbers.

✅ CONCATENATE: Joins two text strings into one. CONCATENATE(Products[Brand], Products[ProductName]) merges the brand and product name.

✅ LEFT, RIGHT, MID: Extract parts of a string from the left, right, or middle. LEFT(Customer[PhoneNumber], 3) extracts the first three digits of a phone number.

✅ LEN: Returns the length of a text string. LEN(Customer[Name]) gives the number of characters in a customer name.

✅ UPPER and LOWER: Convert text to uppercase or lowercase. UPPER(Customer[City]) capitalizes the city name.

Time Intelligence Functions: These functions help you create calculations that use built-in knowledge about calendars and dates. By using time and date ranges in combination with aggregations or calculations, you can build meaningful comparisons across comparable time periods for sales, inventory, and so on.

✅ TOTALYTD: Calculates the year-to-date total. TOTALYTD(SUM(Sales[TotalAmount]), Dates[Date]) gives the sales total from the start of the year to the current date.

✅ SAMEPERIODLASTYEAR: Compares current period data with the same period from the previous year. SAMEPERIODLASTYEAR(Dates[Date]) is useful for year-over-year analysis.

✅ PARALLELPERIOD: Shifts dates to a parallel period in the past or future. For example, PARALLELPERIOD(Dates[Date], -1, YEAR) shifts dates back by one year.

✅ DATESBETWEEN: Returns a range of dates between two specified dates. DATESBETWEEN(Dates[Date], DATE(2024, 1, 1), DATE(2024, 12, 31)) provides all dates in 2024.

Statistical Functions: These functions calculate values related to statistical distributions and probability, such as standard deviation and number of permutations.

✅ MEDIAN: Returns the median value of a column. MEDIAN(Sales[TotalAmount]) calculates the middle value of the sales amount.

✅ STDEV.P and STDEV.S: Calculate the population and sample standard deviations. These are useful for measuring data variability.

✅ PERCENTILE.INC: Returns the percentile of a data set. PERCENTILE.INC(Sales[TotalAmount], 0.9) calculates the 90th percentile of sales amounts.

Table Manipulation Functions: These functions return a table or manipulate existing tables.

✅ ADDCOLUMNS: Adds calculated columns to a table. For example, ADDCOLUMNS(Products, "ProfitMargin", Products[Profit] / Products[Sales]) adds a new column for profit margin.

✅ SUMMARIZE: Groups data by one or more columns and performs calculations on each group. SUMMARIZE(Sales, Sales[Category], "Total Sales", SUM(Sales[TotalAmount])) groups sales by category and calculates total sales.

✅ CROSSJOIN: Returns a Cartesian product of two or more tables, combining all rows. CROSSJOIN(Customers, Products) creates all possible combinations of customers and products.

✅ UNION: Combines rows from two or more tables into a single table. UNION(Table1, Table2) merges the rows from Table1 and Table2.

✅ INTERSECT: Returns rows that exist in both tables. INTERSECT(Table1, Table2) gives the rows common to both tables.

Financial Functions: These functions are used in formulas that perform financial calculations, such as net present value and rate of return.

✅ XNPV: Returns the net present value for a series of cash flows that occur at irregular intervals. XNPV(DiscountRate, CashFlows[Amount], CashFlows[Date]) calculates the net present value using specific dates for each cash flow.

✅ XIRR: Returns the internal rate of return for a series of cash flows that occur at irregular intervals. XIRR(CashFlows[Amount], CashFlows[Date]) gives the IRR for a series of investments or payments.

✅ PMT: Calculates the payment for a loan based on constant payments and a constant interest rate. PMT(InterestRate, NumberOfPeriods, LoanAmount) returns the monthly payment for a loan.