#Summarise dax full#
#Summarise dax code#

The result is a table with the same number of rows as Sales, thus potentially very large. The presence of Sales guarantees that all the rows are retrieved – otherwise, VertiPaq itself would perform a grouping operation. Out of Sales, DAX retrieves Sales, Sales, and Product. To perform the grouping, DAX materializes the required columns of Sales in a datacache, which is then processed by the formula engine. GROUPBY scans the Sales table and groups it by Product. However, because of its behavior, new columns need to be computed as simple aggregations of the columns in the table being grouped using the CURRENTGROUP special function.Īs an example, let us look at the following code: GROUPBY can also add new columns to its result. GROUPBY does not even try to push the calculation down to the storage engine: the entire calculation happens in the formula engine after materializing the table. However, its behavior is very different from that of SUMMARIZE. The column can be a model column or a local column. GROUPBY groups a table by one of its columns. Besides, also in this case, the calculation would require the materialization of the entire Sales table.

Clustering produces surprising results in multiple scenarios, and it is a functionality that creates more problems than solutions. However, remember that despite the query working from a syntactical and semantical point of view, its result is computed by using clustering. The result shows the sales amount grouped by transaction size.

The following query works just fine, despite grouping by a local column: Thanks to clustering, SUMMARIZE can perform grouping by local columns too. The same query, using SUMMARIZE and ADDCOLUMNS, produces the expected result: However, because all the columns of the table are used as group-by columns, the size is often very relevant. Please note that RowNumber is not part of the query, so the granularity of the datacache is not exactly the granularity of Sales, as is the case with GROUPBY. To compute Sales All Brands, this is one of the VertiPaq queries being executed:

#Summarise dax full#

Moreover, because of clustering, as soon as the measure to aggregate is non-trivial, SUMMARIZE needs to materialize the full table. A different data distribution or the presence of duplicated rows might result in different values. Because of clustering, the filter placed by SUMMARIZE affects all columns of the expanded Sales table, resulting in this strange outcome.Īs you see, Sales All Brands repeats the same values as Sales Amount. However, this speculation does not consider clustering. It would be reasonable to expect Sales All Brands to produce the grand total of sales, as CALCULATE removes the only filter present in the filter context. Indeed, as we mentioned SUMMARIZE performs its calculation using a special technique named clustering, described in the article cited earlier.

#Summarise dax code#

However, this simple behavior is easily lost as soon as the code of the measure being executed becomes a bit more complex. $Expr0 := ( PFCAST ( 'Sales' AS INT ) * PFCAST ( 'Sales' AS INT ) ) The storage engine query is the following: SUMMARIZE scans Sales, groups it by Product, and produces the sales amount by brand.

For example, the following code works just fine, producing the expected storage engine queries: When used with simple examples, SUMMARIZE behaves nicely, pushing the grouping operation down to the storage engine. However, for the sake of this comparison, we will use SUMMARIZE to compute new columns to describe its peculiar behavior. Specifically, SUMMARIZE implements clustering, a grouping technique that – despite being very powerful – may lead to unexpected results and poor performance. In that article, we describe the behavior of SUMMARIZE and why it should never be used to compute new local columns. We already wrote about SUMMARIZE in a long and deeply technical article: All the secrets of SUMMARIZE. SUMMARIZE performs two operations: grouping by and adding new local columns. Let us now elaborate on how these functions work to provide more technical information about the previous statement. However, using the wrong function translates into a strong decrease in performance. Be mindful that both functions support both scenarios: both functions can group by model and local columns. SUMMARIZE should be used to group by model and query columns. To make a long story short: GROUPBY should be used to group by local columns, columns created on the fly by DAX functions. However, the article is about SUMMARIZE and GROUPBY, as the other functions have many more functionalities, so a comparison would be unfair.

These are not the only two: SUMMARIZECOLUMNS and GROUPCROSSAPPLY perform similar operations. Among the many, two functions perform grouping: SUMMARIZE and GROUPBY. DAX offers a rich set of functions, some of which overlap in their functionalities.