Project Botticelli

Status message

See the new version of this article on Tecflix.com! Why? Read our announcement.

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

20 November 2013 · 3 comments · 4225 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 access below to the premium version of this content.

Purchase A Full Access Subscription

Subscription Best Value

$480/year

Access all content for 1 year.
  Purchase on Tecflix

  • Payment is instant and you will receive a tax invoice straight away.
  • Your satisfaction is paramount: we offer a no-quibble refund guarantee.

Comments

kelly.darren · 20 April 2015

Hi,
In relation to the Many to Many relationship the use of SUM or COUNTROWS works very fast, but when using COUNTDISTINCT(Fact_Transaction[ID_Account]) , the calculation crawls which sliced on Customer, works fine when not sliced.

From reading posts and white papers the use of CountDistinct and its slowness is expected, as I understand it that the ID_Account field in the bridge table has to be sorted first.

Therefore using the above example how would you calculate the Distinct Number of Accounts when also sliced by Customer.

Thanks
Darren

Alberto Ferrari · 21 April 2015

DISTINCTCOUNT is expected to be slower than SUM. Beware that in SQL 2014 it greatly improved (SQL 2012 requires a cumulative update to obtain better performance). The complexity of M2M depends on the size of the bridge table. The larger the bridge, the slower your response time will be.
In order to give you a better help, I would need to dive more into your solution. Nevertheless, keep in mind that M2M are always challenging, from the performance point of view.

goranbanjac · 29 November 2015

Hi Alberto,

I have a question in regard to dimensional model and many to many relationship in tabular mode. In your document "The many to many revolution" you are giving an example of "survey" cube design in multidimensional model. Does the same dimensions design principals applies in tabular mode as well (in document, you are creating "resulting dimensions" f1, f2 and so on) ? What would be best design practices of "survey" model you described in your document, but for tabular mode?

Thank you!