![]() ![]() We can do that by storing the result of GENERATED in the ProductsCustomers variable that we reference in SUMMARIZE and we pass as a filter argument to CALCULATE to evaluate the Margin measure in ADDCOLUMNS. Thus, in order to generate the equivalent result by using ADDCOLUMNS, it is necessary to bring the same filter obtained by GENERATE in the following Margin measure evaluation. If you wrap the SUMMARIZE into an ADDCOLUMNS, the extended columns created in ADDCOLUMNS work on a filter context defined by Product and Customer, considering many more sales than those originally used by the initial query. Only these customers were considered in the Margin calculation inside SUMMARIZE, because SUMMARIZE was using only those customers filtered by TOPN. This is because this query is computing the Margin measure in a filter context that filters only Product and Customer, ignoring the filter of the top 2 customers that used in the table argument of SUMMARIZE, where the result of GENERATED included all the columns of Product and Customer but just for the top 2 customers for each country. Thus, a category might contain several customers, but no more than 2 per product:Īs you can see, the results are different as Margin is higher than the initial result. The following query returns – by Product Category and Customer Country – the profit made by the top 2 customers for each product. For example, when you apply filters over columns that are not included in the grouped column and then calculate the extended column expression using data coming from related tables, the filter context will be different between SUMMARIZE vs. Just pay attention to the caveats in the following section! Filter Context in SUMMARIZE and ADDCOLUMNSīy describing the pattern of creating extended columns using ADDCOLUMNS instead of SUMMARIZE we mentioned that there are conditions in which you cannot do this substitution – the result would be incorrect. Thus, as a rule of thumb, wrap any expression for an extended column within a CALCULATE function whenever you move an extended column out from SUMMARIZE into an ADDCOLUMN statement. This is because it returns the number of rows in the entire Store table for each row of the result instead of returning the number of stores for each country. If you rewrite this query by simply moving the Stores extended columns out of the SUMMARIZE into an ADDCOLUMNS function, you obtain the following query that produces the wrong result. Now, consider the following query that you have already seen at the beginning of this article. The previous examples used a scalar expression over a column that was included in the SUMMARIZE output, so the reference to the column value was valid within the row context. The reason is that ADDCOLUMNS operates in a row context that does not automatically propagate into a filter context, whereas the same within a SUMMARIZE is executed into a filter context corresponding to the values in the grouped columns. The CALCULATE you can see in the best practices template above is not always required, but you need it whenever the contains an aggregation function. The best practice is that, whenever possible, instead of writing You are using non-trivial table expressions in the extended column, as you will see in the “Filter Context in SUMMARIZE and ADDCOLUMNS” section later in this article.You want to use ROLLUP over one or more grouping columns in order to obtain subtotals.The rule of thumb is that you should never add extended columns by using SUMMARIZE, unless it is required due to at least one of the following conditions: However, you should always favor the ADDCOLUMNS version. For example, the following query adds an Open Year column to the rows returned from the Store table.Ĭontoso Store Australian Capital Territory You can obtain extension columns by using both ADDCOLUMNS and SUMMARIZE. Extension ColumnsĮxtension columns are columns that you add to existing tables. You can also read the All the secrets of Summarize article for more insights about inner workings of SUMMARIZE. ![]() For DAX queries, you should consider using SUMMARIZECOLUMNS, starting with the Introducing SUMMARIZECOLUMNS article. This article describes how to use ADDCOLUMNS and SUMMARIZE, which can be used in any DAX expression, including measures. However, in its current implementation DAX does not permit all the operations that you can perform in SQL. Because of the similarities between Tabular data modeling and relational data modeling, there is the expectation that you can perform the same operations as those allowed in SQL. UPDATE : Fixed an incorrect description before example #11.Įveryone using DAX is probably used to SQL query language. UPDATE : The article has been updated using DAX.DO for the sample queries and removing the outdated part.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |