SUMMARIZE with a single column will give the same result with the same performance as DISTINCT, so no blanks. However, the VALUES function can also return a blank value.” Both functions remove duplicates and return a list of the possible values in the specified column. By the VALUES function documentation: “In most scenarios, when the argument is a column name, the results of the VALUES function are identical to those of the DISTINCT function. With a single column, it would be possible to use DISTINCT or VALUES too.
SUMMARIZE function alternatives with a single column Table_sales_per_customer_table_with_summarize = SUMMARIZE( not recommended if any more complex measures or filters in measures "sales_events" SUMX(RELATEDTABLE(Sales) 1) Table_sales_per_customer_table_with_addcolumns = ADDCOLUMNS ( VALUES can be replaced with SUMMARIZE (or DISTINCT) Here are three ways to create the same table with dax, with preference on alternatives table_sales_per_customer_table_with_summarizecolumns and table_sales_per_customer_table_with_addcolumns: table_sales_per_customer_table_with_summarizecolumns = SUMMARIZECOLUMNS( Three ways to create the same table with DAX in the example 2 code, it would NOT be possible to call the function below in the end, it will lead to an error “ table cannot be used because a base table is expected”: CALCULATE(SUM(sales_per_customer_table_expression_with_addcolums)
#Dax summarize code#
If one builds a table expression in a measure with a variable, like with SUMMARIZE in the code examples of this post, one cannot use this table expression variable like a physical base table later in the measure. SUMMARIZE as a variable in a measure cannot be used as a base table It is safer to always use ADDCOLUMNS + SUMMARIZE structure. The same post and linked articles to it point out that one should not calculate values directly with SUMMARIZE especially due to its handling of filters in the measures used inside the SUMMARIZE calculations. You can read more about SUMMARIZECOLUMNS vs SUMMARIZE in this post by SQLBI. If you do not calculate anything with SUMMARIZE (= just selecting a list of dimensional values for example) there should not be any performance difference to SUMMARIZECOLUMNS. Example 2 would work with only the end result of the measure used as a single figure without any row evaluation.
This makes the use of SUMMARIZECOLUMNS not possible at all in the case of the code example 1, and in the code example 2 in the case of showing data in a categorical graph or a table. There are some differences like SUMMARIZECOLUMNS not having a row context like SUMMARIZE. Table_expression_filter FILTER('Calendar' 'Calendar'=2004))ĭAX SUMMARIZECOLUMNS function should be preferred over SUMMARIZE due to its better performance. "sales_events" SUMX(RELATEDTABLE(Sales) 1) -same as COUNTX but better performanceįILTER(sales_per_customer_table_expression_with_addcolumns >1000) "Sales Amount sum" SUMX(RELATEDTABLE(Sales) Sales) VAR sales_per_customer_table_expression_with_addcolumns = Measure KPI customer count sales 2004 over 1000 eur = The basic function pattern is DAX ADDCOLUMNS with SUMMARIZE. too instead of UNION in another type of requirement setting here would be possible to use EXCEPT or INTERSECT functions can have in 2004 same customer and we want to count it only 1 time above union of all customer keys, then distinct as e.g. VAR other_customers = -last 2 quarters, in this example Q3 and Q4ĬALCULATETABLE(SUMMARIZE ( Customers Customers ) įILTER(dimProductCategory dimProductCategory"Bikes" &ĭimProductCategory"Accessories") įILTER('Calendar' 'Calendar'=2004) FILTER('Calendar' 'Calendar'="Q3" || 'Calendar'="Q4"))ĭISTINCT(UNION ( bikes_customers accessories_customers other_customers)) CALCULATETABLE is needed to filter based on two tables VAR accessories_customers = -latest year, in the example 2004įILTER(dimProductCategory dimProductCategory="Accessories") įILTER('Calendar' 'Calendar'=2004)) Measure KPI customer count =ĬALCULATETABLE(SUMMARIZE ( Customers Customers) įILTER(dimProductCategory dimProductCategory="Bikes")) The basic function pattern is DAX CALCULATETABLE with SUMMARIZE. How section considers whether DAX is the best solution in the first place. In the theory section is discussed alternatives and additional approaches and their differences. In this post I present two function patterns to handle most of this type of situations. An example could be a KPI like the customer count of a company (per product) when different products have differences in the counting logic or data tables. Often there is a need to (distinct) count or sum values based on multiple filtered tables over a selected variable like a product type.
0 kommentar(er)
