DAX Patterns: Banding, New vs Old, Many-to-many

20 November 2013 · 3 comments · 2914 views

The Most Common Patterns and Formulas

Alberto Ferrari Explains Many-to-many DAX Pattern

Alberto Ferrari, BI expert from sqlbi.com shows how to solve common business patterns using the DAX language in this 50-minute, demo-focused video. Patterns are useful for two reasons: they let you learn advanced DAX techniques and, by adapting their code to your needs, you can use them as a quick recipe for your own scenarios. This video discusses banding, computing new vs old metrics, and many-to-many relationship patterns.

Banding is a very common technique. You define a range of data points, such as a price range, an income range or another general grouping of values. Banding can be static or dynamic. Static banding requires only basic DAX code, and a calculated column, while dynamic banding requires more complex DAX, which is also an example of a non-additive formula computed in DAX. Static banding is also a good example of how a complex relationship can be consolidated in a calculated column, by using CALCULATE and a suitable filter that mimics the relationship.

The second pattern, frequently seen in common business reports, computes a new vs old metric aggregation, for example, counting purchases made by new vs returning customers. A new customer is somebody who buys for the very first time. A returning customer, on the other hand, is a customer who already bought something. This is the same pattern that Alberto has already shown in the DAX in Action video but, this time, he explains the formula in-depth, developing it step-by-step, to give you a full understanding of how it works. As a result, you will be able to modify it to compute lost customers, measure churn, and make other like-for-like comparisons.

The last pattern, many-to-many relationships in DAX, is discussed at great length in this video. In order to understand how the pattern works, Alberto shows different variations of the formula, starting with a slow-but-simple version that uses CALCULATE, FILTER and context transition. Finally you will learn a very-fast-but-cryptic version of the pattern formula based on the usage of SUMMARIZE. It should be useful if you spent time learning those different formulas because each of them has an application for real-world scenarios. Indeed, many-to-many relationships in Tabular can be used to search for both existing and non-existing relationships among data!

Log in or purchase a Full Access Membership to access the premium version of this content.

Buy Full Access Membership

Access all content, including full-length training videos, demo files, and articles, right now! Payment is instant, and you will receive a tax invoice, straight away.

Our content is updated regularly, get the best value by buying a 1 year membership.

Your satisfaction is paramount: we offer a no-quibble refund guarantee.