All Patterns

The dynamic version of the ABC Classification pattern is an extension of the Dynamic Segmentation pattern. It groups items (such as Products or Customers) into segments based on their cumulated sales and how much they contributed to the total sales …

DAX does not directly support parent-child hierarchies. To obtain a browsable hierarchy in the data model, you have to naturalize a parent-child hierarchy. DAX provides specific functions to naturalize a parent-child hierarchy using calculated columns. The complete pattern also includes …

The New and Returning Customers pattern dynamically calculates the number of customers with certain behaviors based on their purchase history. This calculation can be computing-intensive and, thus, a critical part of the pattern is an efficient implementation.

The Basket Analysis pattern enables analysis of co-occurrence relationships among transactions related to a certain entity, such as products bought in the same order, or by the same customer in different purchases. This pattern is a specialization of the Survey …

The Survey pattern uses a data model and a DAX expression to analyze correlation between different transactions related to the same entity, such as a customer’s answers to survey questions.

The budget patterns are techniques you use to compare budget information with other data. They are an extension of the handling of different granularities and, as such, use allocation algorithms to display the budget at granularities for which it is …

The Handling Different Granularities pattern is a technique to show and hide measures depending on the level of granularity of the underlying data. For example, you do not want to display a measure at the day level if it is …

The ABC Classification pattern is a specialization of the Static Segmentation pattern that implements the ABC analysis in DAX, which is also known as ABC/Pareto analysis, because it is based on the Pareto principle. The resulting ABC class is calculated …

The DAX time patterns are used to implement time-related calculations without relying on DAX time intelligence functions. This is useful whenever you have custom calendars, such as an ISO 8601 week calendar, or when you are using an Analysis Services …

DAX includes a few statistical aggregation functions, such as average, variance, and standard deviation. Other typical statistical calculations require you to write longer DAX expressions. Excel, from this point of view, has a much richer language. The Statistical Patterns are …

The Dynamic Segmentation pattern alters the calculation of one or more measures by grouping data according to specific conditions, typically range boundaries for a numeric value, defined in a parameter table (see the Parameter Table pattern). Dynamic segmentation uses the …

The Static Segmentation pattern groups a continuous numeric value in a finite number of clusters by using a configuration defined in a parameter table (see also Parameter Table pattern). The static segmentation happens in a calculated column that is useful …

The Parameter Table pattern is useful when you want to add a slicer to a PivotTable and make it modify the result of some calculation, injecting parameters into DAX expressions. To use it, you must define a table that has …

The Related Distinct Count pattern allows you to apply the distinct count calculation to any column in any table in the data model. Instead of just counting the number of distinct count values in the entire table using only the …

The Cumulative Total pattern allows you to perform calculations such as running totals, and you can use it to implement warehouse stock and balance sheet calculations using the original transactions instead of using snapshots of data over time.