A few days ago I published a new article on DAX Patterns web site describing how to implement Basket Analysis in DAX. This topic is a very classical one and is also covered in the many-to-many revolution white paper. It has been also discussed in several blog posts, listed here in historical order:
- Simple Basket Analysis in DAX by Chris Webb
- PowerPivot, basket analysis and the hidden many to many by Alberto Ferrari
- Applied Basket Analysis in Power Pivot using DAX by Gerhard Brueckl
As usual, in DAX Patterns we try to present the required DAX formulas in a way that is easy to adapt to specific models. We also try to show a good implementation from a performance point of view. Further optimizations are always possible in DAX. However, in order to keep the model simple to adapt in different scenarios, we avoid presenting optimizations that would require particular assumptions or restrictions on the data model.
I hope you will find the Basket Analysis pattern useful. Even if you do not need it today, reading the DAX formula is a good exercise to check your knowledge of evaluation contexts in DAX. For example, describing how does it work the following expression is not a trivial task!
[Orders with Both Products] :=
DISTINCTCOUNT ( Sales[SalesOrderNumber] ),
SUMMARIZE ( Sales, Sales[SalesOrderNumber] ),
ALL ( Product ),
USERELATIONSHIP ( Sales[ProductCode], ‘Filter Product'[Filter ProductCode] )
The good news is that you can use the patterns even if you do not really understand all the details of the DAX formulas you are using!
Any feedback on this new pattern is very welcome.
Evaluates an expression in a context modified by filters.
CALCULATE ( <Expression> [, <Filter> [, <Filter> [, … ] ] ] )
Counts the number of distinct values in a column.
DISTINCTCOUNT ( <ColumnName> )
Evaluates a table expression in a context modified by filters.
CALCULATETABLE ( <Table> [, <Filter> [, <Filter> [, … ] ] ] )
Creates a summary of the input table grouped by the specified columns.
SUMMARIZE ( <Table> [, <GroupBy_ColumnName> [, [<Name>] [, [<Expression>] [, <GroupBy_ColumnName> [, [<Name>] [, [<Expression>] [, … ] ] ] ] ] ] ] )
Returns all the rows in a table, or all the values in a column, ignoring any filters that might have been applied.
ALL ( [<TableNameOrColumnName>] [, <ColumnName> [, <ColumnName> [, … ] ] ] )
Specifies an existing relationship to be used in the evaluation of a DAX expression. The relationship is defined by naming, as arguments, the two columns that serve as endpoints.
USERELATIONSHIP ( <ColumnName1>, <ColumnName2> )