The Most Common Patterns and Formulas
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.
Many-to-many Relationships in DAX
DAX: Data Analysis Expressions (Older)
1.
DAX in Action! 19-minFree
2.
DAX: Calculated Columns vs. Measures 11-minFree
3.
Introduction to DAX in Excel 37-min
4.
DAX Evaluation Context 51-min
5.
DAX: The CALCULATE Function 47-min
6.
Querying with DAX 46-min
7.
What is Time Intelligence? 2-minFree
8.
DAX Time Intelligence 1-hour
9.
Many-to-many Relationships in DAX 3-minFree
10.
DAX Patterns: Banding, New vs Old, Many-to-many 51-min
11.
How to Calculate with Hierarchies in DAX? 1-minFree
12.
Hierarchies in DAX 54-min
13.
DAX AW Sample Data Set
Purchase A Full Access Subscription
- Payment is instant and you will receive a tax invoice straight away.
- Your satisfaction is paramount: we offer a no-quibble refund guarantee.
Jump to a chapter
- Introduction (00:08)
- Banding Pattern (00:48)
- Demo: Calculating Price Bands (02:26)
- Demo: Dynamic Banding (11:03)
- New vs Returning Customers Pattern (18:27)
- Demo: Counting New and Returning Customers (19:13)
- Demo: Counting Only New Customers (20:35)
- Many-to-many Relationship Pattern (30:06)
- Exceptions to Many-to-many (31:31)
- Demo: Working with Many-to-many in Financial Data (31:58)
- How Does Many-to-many Formula Work? (36:20)
- An Inefficient Form of Many-to-many (38:07)
- Comparing Correct and Incorrect Many-to-many Formulas (38:45)
- "Wrong" Formula in Action (39:40)
- Correct Many-to-many in Action (42:24)
- SUMMARIZE for Efficient Many-to-many Pattern (45:54)
- Direct Table Filtering (48:11)
- Drawbacks to Many-to-many (50:06)
- Summary (50:53)
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!