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 … Read more
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.
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 … Read more
IMPORTANT If you use Power BI, Analysis Services, or Excel 2016 or later versions, you can use the statistical functions in DAX. If you use Excel 2010 or Excel 2013, most of the DAX statistical functions are not available and … Read more
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 … Read more
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 … Read more
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 … Read more
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 … Read more
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 … Read more
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 … Read more
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 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 … Read more
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 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 … Read more
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 … Read more
