. 在本章中，我们将继续探索dax语言的强大功能，并详细说明一个函数：calculate。相同的注意事项适用于 calculatetable，它计算并返回表而不是标量值。为了简单起见，我们将在示例中引用 calculate，但是请记住 calculatetable 显示相同的行为。CALCULATETABLE with multiple filters 10-03-2022 04:18 PM. Calculate Function: Syntax: CALCULATE (<Expression>,<Filter 1>,<Filter 2>. If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Once this evaluation is finished, CALCULATE starts building the new filter context. Andy by itself, FILTER creates a row context whereas CALCULATETABLE does not. CALCULATETABLE 1️⃣ CALCULATE Function: CALCULATE is go-to function for modifying filter contexts and performing calculations based on. [Date], data [Product], data [ProductCategory], "Amount", SUM ( data [SalesAmount] ) ) If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Sorted by: 1. ) サンプルとして売上の合計金額出してみます。. MEASURE Customer[# Customers] = COUNTROWS (. Internal behavior of LOOKUPVALUE. I build 2 tables from fact_Sales to get all dates. FILTER takes a table expression as its first argument and iterates through all the rows of that table checking the condition provided in the second argument. Return value. For the Sales PD scenario, we used the following query to perform the benchmark: 1. When you add two filters to the CALCULATE Function, this is equavalient to using AND. Basically, ALL returns a table including all rows, ignoring any filters that might have been applied. The lookup functions work by using tables and relationships, like a database. Summarize. Modify your code to fix this issue. There are also restrictions applied to arguments that are Boolean expressions: the expression cannot reference a measure, it cannot be used as a nested CALCULATE function nor it can be used in any function that scans or returns a. It does not materialize the resulting table when called directly in a filter argument of CALCULATE or CALCULATETABLE. 02-10-2023 01:48 PM. 📊 #50DAXFunctionChallenge DAX Day 9: CALCULATE vs. They are usually referred to as the ALLxxx functions. But if you use the CALCULATETABLE function, the query in the. Ruth Pozuelo. PersonaPortfolio = a table that is filtered down based on the USERPRINCIPALNAME. See below the. In this scenario, I would suggest you to create a measure to calculate the "Total Sales" with corresponding conditions, then show the measure on a Table visual with other columns. . In my calculate table I have to take all those records from master table, when the below two conditions are met: 1. Measures used: SAO Opportunities = CALCULATETABLE CALCULATETABLE is the table-version of CALCULATE. ' CALCULATETABLE triggers context transition whereas FILTER does not. Each row represents a job logged by the technician and includes the number of hours logged for the job. Difference Between CALCULATE & CALCULATETABLE DAX Functions: Both Functions are similar but not the same. Step-1: Go to Modeling Tab > Select “DAX expression to create a new table”. but to get the equivalent with CALCULATETABLE you would have to preface that function with something like a SUMX. It’s an amazing way to get lots of calculations done in your model without taking up room. The same filter applied on Date by using CALCULATETABLE propagates to the Sales table as because of the relationship existing between the two tables. The syntax of the CALCULATETABLE function is usually easier to understand than the. The following expressions are equivalent. Let’s say my explanation in the previous paragraph left you feeling a bit lackluster and you want to SEE the [SalesTotal] for each combination of Year and Quarter in a table. Tabuľka hodnôt. This function can be used to obtain visual. . In order to Sum the sales amount of blue products OR products that belong to category shoes, I'm using the following DAX expression: CALCULATE ( SUM (Table [SalesAmount]), FILTER ( Table, Table [Color] = "Blue" || Table [Category] = "Shoes") ) However, this doesn't work with two different. Now my purpose is to identify Customer_id which are common (having sales at those 2 dates)Gain a 360° of how to explore and use Power BI to build impactful reports. Add a comment. This video helps you to understand the filter and value functions in DAX. g. A table with the same number of rows as the table specified as the first argument. If a table has more than one segment, the calculated columns will be split into the same number of segments. For example, if I create a calculated column and enter this formula =CALCULATE(SUMX(Table1, Table1[Field1]*Table1[Field2]) then I know the current row in the calculated column gets. In this video I will show you how to use CALCULATE as an equivalent of the excel functions SUMIF and COUNTIF. I have a simple fact table on sales per customer_id & date. That means, if in your data model relationship is present, then this. Message 2 of 3. If the REMOVEFILTERS function is supported by your tool, it's better to use it to remove filters. 1 Funkcia ALL a jej varianty sa správajú ako modifikátory filtrov aj ako funkcie, ktoré vracajú objekty tabuľky. CALCULATETABLE(), of course, will modify the filter context – whereas FILTER() is an iterator function. . End of year date. This is always the case for most of the DAX functions, but not for CALCULATE and CALCULATETABLE. CALCULATETABLE triggers context transition whereas FILTER does not. They are typically the contents of a diagram. Thank you. Measure = var fitler1 = CALCULATETABLECreating advanced calculations like new vs. ”. DEFINE. In both Power BI and Excel, all the elements selected in a slicer are considered in a logical. Part of Microsoft Azure Collective. The result can be assigned to a variable, because TREATAS is not a filter modifier. Copy Conventions # 1. This video will walk thru a practical example of using these functions as filters. iterate over the Transactions table, stepping row by row. CALCULATETABLE 1️⃣ CALCULATE Function: CALCULATE is go-to function for modifying filter contexts and performing calculations based on. UPDATE 2022-02-11 : The article has been updated using DAX. com FILTER vs CALCULATETABLE: optimization using cardinality estimation A common best practice is to use CALCULATETABLE instead of FILTER for performance reasons. Andy by itself, FILTER creates a row context whereas CALCULATETABLE does not. Table 2 = SUMMARIZE ('Table1',Table1 [Area],'Table1' [Store Code]) Best Regards, Qiuyun Yu. -- and COUNTROWS. This function is a shortcut for CALCULATETABLE function with no logical expression. . Message 8 of 12. AddColumns is used to add aggregations to data created by Summarize function. When iteration is not required, try your best to use CALCULATE or CALCULATETABLE because FILTER, as mentioned, is an iterator which might cause performance issue. I’m adding filter context on both measures and the filter context is the same. The main difference between the two is that the CALCULATETABLE. Sum and SumX both are functions calculating aggregation. When to use a calculated column. A filter context is a set of filters over the rows of the data model. In this article, we provide an introduction to CALCULATE, its behavior, and how to use it. I calc VarFirstDate,VarLastDate and new variable VarSelectedCountry:. . . The filter and value functions in DAX are some of the most complex and powerful, and differ greatly from Excel functions. The relationship is defined by naming, as arguments, the two columns that serve as endpoints. The values are then averaged to return the correct value. Since you put the code of calculate table in the VAR function, the formula will been limiting calculate range on current row. The measure [TotalSales] inside AVERAGEX is called in a filter context that filters only one year. CALCULATE calculates a scalar expression (first argument) in the filter context determined by the remaining arguments. View solution in original post. This article describes its internal behavior, and provides guidance on how to use it. While CALCULATE allows you to modify calculations based on specific conditions, CALCULATETABLE goes a step. . Rank all rows as Column = RANKX ( 'Table', 'Table' [My Value] + (INT ('Table' [Date]) / 100000) ) This produces a unique ranking for each row of the table, based on the My Value column that uses the Date column to split ties. My approach was to calculate the table of results that I need to count. In that video, there was also a discussion of a Microsoft document that. . Improve this answer. power is a strongest tool in market wh. The performance of that is the same as my CALCULATE version in my testing. CALCULATE and CALCULATETABLE, on the other hand, first apply all the filters and then evaluate the expression. SummarizeColumns is newer function that does formula from above. Its use is very intuitive at first, and most DAX developers start using. 14. You will use the SUM () function for this measure. Not recommendedSummarize is used for grouping fields from dimension, but not for aggregating data. Churn analytics involves the evaluation of a company’s customer loss rate. Term Definition; table: The table containing the rows for which the expression will be evaluated. EXCEPT removes the rows of the second argument from the first one. Problem statement: I have a table (MyTable) of descriptions and a month for the given description. This function is a shortcut for CALCULATETABLE function with no additional filters, accepting only a table reference and not a table expression. For a final challenge, see if you can write a cumulative version without CALCULATE or CALCULATETABLE that refreshes in under 10 seconds with no slicer filtering. There might be different ways to calculate and visualize it, but I want to do it with calculated table as a sample scenario. or i want my top 10 inside a table CALCULATETABLE( TOPN( 10,. The name given to a total or summarize column, enclosed in double quotes. -- In DAX, there are no differences between COUNTA and COUNT. There might be different ways to calculate and visualize it, but I want to do it with calculated table as a sample scenario. #powerbitelugu #daxtelugu Calculate : is a filter modifier that does not remove an existing column or table filter in the filter context that conflicts with the filter applied by the argument of KEEPFILTERS used as: a filter argument in CALCULATE / CALCULATETABLE. The CALCULATE function allows you to change the context of a calculation within a measure wherein the context is coming from the environment where your calculation is being done. 2. FILTER () returns a list of Stores, which are above the threshold. I need to create an aggragated table based on a table within my Power BI app. is equivalent to. LASTDATE returns a table, not a scalar value, even if it is a table containing only one row, which can be converted into a scalar value. . g. The context of the cell depends on user selections in the. 📊 #50DAXFunctionChallenge DAX Day 9: CALCULATE vs. CALCULATETABLE ( DISTINCT ( <ColumnName> ) ) A table expression that returns a single column. 2 Answers. CALCULATE: Evaluates an expression in a context modified by filters. ). 👇🏽Clic Para Mas 👇🏽🔴 SUSCRIBETE al Canal para mas v. Chapter 5. A common best practice is to use CALCULATETABLE instead of FILTER for performance. -- It provides most querying features in a single function: -- First set of arguments are the groupby columns. They are very similar in some aspects but also very different in others. Step-3: As you can see in below. A column or table is said to be cross-filtered when a filter is applied to any column of the same table or in a related table. As a business requirement I want to visualize the total revenue from top 100 customers and compare it with total revenue of the whole business. SUMX (. Hopefully this clears up some of the differences between CalculateTable and Filter. Andy by itself, FILTER creates a row context whereas CALCULATETABLE does not. New Table =CALCULATETABLE ( Workschedule, Filter (All (Workschedule),M [ActivityId]=BLANK ()) If this post helps, please consider accept as solution to help other members find it more quickly. Effectively this ALL () trumps the filter because ALL is ALL, regardless of filters. Visit% this, in turn, is a measure of actual vs plan. Continent = “Europe” from the Customer table. When you run it, the Server Timings will show that the FILTER argument isn’t applied to the xmSQL code. The CALCULATE function in DAX is like a magic wand that allows you to modify or override the regular calculations in your formulas. CALCULATE calculates a scalar expression (first argument) in the filter context determined by the remaining arguments. DAX Optimization - CALCULATE, COUNTROWS, FILTER, RELATEDTABLE. CALCULATE ( <評価式>, <抽出条件1>, <抽出条件2>. The only way to make it work is to build a measure. In this DAX function, you have mentioned the fact table, but for the group by column used order date and product dimensions. ISFILTERED can check whether a column is being filtered directly or if any of the columns of the table is being filtered directly. Create table. This function is not supported for use in DirectQuery mode when used in calculated columns or row-level. CALCULATETABLE ( ADDCOLUMNS (. FILTERS (‘Geography’ [Region Country]) — Parts omitted — EVALUATE. I am trying to write DAX in Calculation Groups to calculate YTD values from previous years combining CALCULATETABLE AND DATEADD. DAX expressions returning different results (CALCULATE vs CALCULATETABLE) 04-27-2023 07:48 AM. When I use SUMX the value returned retains row context and then the original filter (period) in the presentation layer filters (despite my building of a table variable removing period filter, and also wrapping SUMX with a. TableWeekly = CALCULATETABLE( ADDCOLUMNS ( VALUES(Table1[Weekly Check]), "Last Time Checked", CALCULATE. I understand CALCULATE will not return a table whereas CALCULATETABLE and RELATEDTABLE will. COUNTROWS ( DISTINCT ( table [column] ) ) DISTINCTCOUNT ( table [column] ) ) Copy Conventions # 2. It's a bit easier to do in Table tools in the Data View, because then you can immediately see your new calculated table. Power BI Datamart is more like a container around other components of Power BI (Dataflow, Dataset, and Azure SQL Database). The three set functions available in DAX are: UNION, INTERSECT, and EXCEPT. 1. The CALCULATETABLE function is a fundamental component of the Data Analysis Expressions (DAX) language, widely used in Microsoft Power BI and other data. One of the best times to incorporate the use of the CALCULATETABLE DAX function is when you’re trying to analyze your churn analytics. They let you write calculations that remove or add filters, or modify relationship paths. The CALCULATETABLE portion is meant to hide future dates in YTD calculations. 4. D1 date = SUMMARIZE ('Fact Sales'; [Date]) D2 date = SUMMARIZE ('Fact Sales'; [Date]) I used them as slicers to select manually 2 dates. You’ll first learn how to confidently load and transform data using Power Query and the importance of data models, before diving into creating. 2 sec, including all the overhead:ALL without arguments can be used only as a CALCULATE or CALCULATETABLE modifier and removes all the filters from the filter context. 2. Row Context. These are the only DAX functions which can change the existing context. The CALCULATETABLE Function switches the context in which the data is filtered and evaluates the expression in the new. CALCULATETABLE allows you to create virtual tables that you can then filter using multi. CALCULATETABLE function changes the context in which the data is filtered, and evaluates the expression in the new context that you specify. However, the. A closer look at CALCULATETABLE and SUMMARIZE DAX function used as a filter in measures - Power BI. In this example, I will show you how to calculate the min date based on the status column, we can use the calculate filter the date for both the Active and Inactive employees. Summary. I’m not getting the expected output when using CALCULATE. It seems that filter ‘LastDate(test[Date])’ not work. The FILTER () function considers this filter when evaluating the list of stores with more than 100'000'000 sales. With a column, ALLSELECTED returns the values of the column considering only the last shadow filter, if any. CALCULATE functions. 'DATE' [MonthID] - unique ID for every month in the table. 07-28-2021 07:29 AM. . In DAX, how do I RETURN the sum of a calculated column from a DAX Table Variable (created via ADDCOLUMN)? 0. 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. -- to the complexity of the result in some scenarios. SelectColumns Vs. Hi. Example. We go through a brief explanation, as well. Introduction. FILTER vs CALCULATETABLE was a good discussion around the difference. VAR = SUMMARIZE (. 'The purpose of this question is to understand the filter context of calculatetable. This function is a shortcut for CALCULATETABLE function with no logical expression. Later, we will see an example where this function hasn’t the same effect. DAX Fridays #186: CALCULATE vs CALCULATETABLE Curbal 118K subscribers Join Subscribe 15K views 2 years ago DAX Fridays! Do you wonder what is. 使う式によって. In the following description you will see. Even though this function is commonly used for dates, it can be applied to a column of any data type. The Date table must satisfy the following requirements: . Full. LOOKUPVALUE requires a column to retrieve a set of column/value pairs to provide the search conditions, and an optional default value in case there are either no matching rows, or too many matching rows. The data is structured by date, tech, and there are multiple entries for each date for. The summarized table linked to the project table by project ID. name. It's not supposed to ditch those specified columns entirely. A closer look at CALCULATETABLE and SUMMARIZE DAX function used as a filter in measures - Power BI. I am using DAX to calculate 4 different intervals between specific date fields, using DATEDIFF to count the days. To alleviate this confusing behavior of ALL, REMOVEFILTERS was. 1. The next step is to try to filter two columns from two different tables: Brand = “Contoso” in the Product table. . The FILTERS () function returns a table with filtered values in a specified column within the current Filter Context. 3. Event Date. The following measure: Multiple columns in the same predicate should be used only when necessary. When you write a filter argument in CALCULATE, you provide a table that is placed in the filter context. FILTER However, CALCULATETABLE, unlike FILTER, modifies the filter context, and this is the first behavior of this function that we will explore. The VAR needs to be the Churn Time Period Value (user selected). Just like for measures, you can filter data using either CALCULATETABLE or FILTER . So we’re going to create a new measure and call it Cumulative Product Sales. SUMX is a generic and powerful function, that is why we see the usage of that a lot in DAX. Super User. My Calendar table contains task, start date and End Date + more stuff. AddColumns. Filtering during CALCULATETABLE using values from another table (DAX) 0. The result table includes only dates that exist in the dates column. Calculated Column in PowerBI With Filters. Both CALCULATETABLE and FILTER are. 鑒於中文的DAX網路上分享文章太少了，所以就以中文整理近來所學，以及學通的部分做分篇撰寫。 網路上已有多篇文章說明CALCULATE的用法(知乎、SQLBI)，但我還是想以自己工作時較常用的實務邏輯脈絡來做整理。囿於我自用的Excel屬家用版，沒有power pivot的功能，所以文中是以power bi 來建模. Measures used: SAO Opportunities = CALCULATETABLE CALCULATE evaluates all the explicit filter arguments in the original evaluation context, each one independently from the others. RELATEDTABLE returns a table with all the related rows by leveraging context transition. This function is not supported for use in DirectQuery mode when used in calculated columns or row-level security (RLS. My last filer is by CU. All 3 calculations below produce the same result. Provide details and share your research! But avoid. calculate Transactions [Quantity] * Transactions [UnitPrice] at each row. Hope it is clear. If DAX knowledge can be compared to a. 2. Click to read more. I am using the following formula. Open the Power BI desktop and load the data into it. Andy by itself, FILTER creates a row context whereas CALCULATETABLE. . everybody! Please help to fix issues with following DAX formula. As an example, let’s filter the product color to only show Red and/or Black. CALCULATE is the most powerful and complex function in DAX. For example, when you use CALCULATE with a FILTER, you can use functions like SUM, COUNT, etc. Syntax. Just like for measures, you can filter data using either CALCULATETABLE or FILTER . Step-2: After that Write below DAX function. An order can have many order lines. Calculate_Table_with_measure = SUMX ( CALCULATETABLE (Orders, Orders [Sales]. De kan ikke bruge en indlejret CALCULATE-funktion. 4. Power BI tutorial for beginners on how create new custom and filtered table by using dax formula calcualtetable and summarize function that not only create a. CALCULATE: Evaluates an expression in a context modified by filters. FILTER vs CALCULATETABLE was a good discussion around the difference. A Boolean expression that defines a single-column table of date/time values. EVALUATE. ; Returning customers: the number of customers who have already purchased something in the past,. What if I remove CALCULATETABLE from the language. Any existing filters on each column used in a filter argument are removed and replaced with the filter used in the filter argument. The SUM function is similar to the Excel function of the same name, except that it takes a. For a final challenge, see if you can write a cumulative version without CALCULATE or CALCULATETABLE that refreshes in under 10 seconds with no slicer filtering. In reality, the same. KEEPFILTERS ( <Expression> ) USERELATIONSHIP. CALCULATETABLE ( TOPN ( 10,. Andy by itself, FILTER creates a row context whereas CALCULATETABLE does not. CALCULATETABLE function DAX. When you write a measure in DAX, its logic is recalculated in every cell of a visualization. Power BI Calculate Table DAX function tutorial on how to use calculatetable function to create new table or new measure based on condition or criteria. The RELATEDTABLE function is equivalent to CALCULATETABLE minus the. This is the reason why the pivot table shows the value for A or B in the. var customers=ADDCOLUMNS ( DimCustomer, 'Rand', RAND ()) Now the result of the expression above is in a table variable, you can use that to pick the one with the highest random value; var one_Customer=TOPN (1,customers, [Rand],DESC) As you can see, the TOPN function uses the result of AddColumns (the customers variable) as the. The great advantage of working with a standard calendar is that you can rely on several built-in time intelligence functions. Just make sure you use the right variables here. If you like to follow best practices, you can just read this paragraph out of the entire article. CALCULATETABLE can be a mind-bender when you're just starting out with Power BI. Based on my understanding, the "corrected hours of max 8 hours" means the max hour value is 8 even if one employee's working hours in one day is larger than 8 hours, right?If so, you can add a calculated column in original table to generate the corrected hours. Here is a stripped down version of what I am trying to do. Here is where you will use the CALCULATE () function. In other words, it returns a table or table expression where the filter on the table has been modified in some way. Removes context filters from columns and rows in the current query, while retaining all other context filters or explicit filters. Apr 30, 2021. As you can easily check, this time ALLSELECTED does not restore the original filter context. The RELATEDTABLE function performs a context transition from row context (s) to a filter context, and evaluates the expression in the resulting filter context. ALL ('GDO SD Ticket. In order to understand the behavior of ALLSELECTED, we use a very simple data model, containing a single table (called T) with only three rows and two columns (Col and Val): Based on this table, you can easily create a pivot table with a slicer that selects only two rows and the Sum of Val in the value area: The RELATEDTABLE function changes the context in which the data is filtered, and evaluates the expression in the new context that you specify. CALCULATE DAX function's output would be a. So, the table expression in your formula is ALL (table). 1. ' CALCULATETABLE triggers context transition whereas FILTER does not. The video has a noticeable number of views and comments so is of interest to the community. The CALCULATETABLE Function switches the context in which the data is filtered and evaluates the expression in the new context. CALCULATE or CALCULATETABLE) to SUMMARIZECOLUMNS, so you cannot use it in a measure expression. Dataanalytics interview Questions. A closer look at CALCULATETABLE and SUMMARIZE DAX function used as a filter in measures - Power BI. The syntax of the CALCULATETABLE function is usually easier to understand than the FILTER function syntax. When MAXA operates with a Boolean data type, it consider TRUE as 1 and FALSE as 0. ). RELATEDTABLE function changes the context in which the data is filtered and evaluates the function in the new specified context, just like using CALCULATE or CALCULATETABLE. To alleviate this confusing behavior of ALL, REMOVEFILTERS was. So if you wanted to get the Direct or Referral Channels. RELATEDTABLE is an alias for CALCULATETABLE, added to the DAX language to be the companion of RELATED and to increase readability. The answer here was to (i) insert a CALCULATETABLE earlier with a filter to reduce the processing load, and (ii) use COUNTAX. Calculatetable dax result. I need a dynamic table that chnages the list by the date range slicer on the report. Make sure to wrap CALCULATE () over any calculations you are doing within the table being filtered on. The measures in this pattern work on a regular Gregorian calendar with the following assumptions: Years and quarters always start on the first day of a month. 1 ACCEPTED SOLUTION. Also, conditions between columns should be expressed as separate predicates. But you can create a filter using FILTER to filter your table by your measure value. You create another measure that narrows down the summarised Sales figures to reflect only grouper sales. FILTER (. potential performance issues per his article I referenced would be one thing I would think. EVALUATE. Solved: This works perfectly if TableB is a physical table: Measure= CALCULATE([Measure], TableA[ID] IN VALUES(TableB[ID])) But, how to calculate theI have a header-detail type of table for Orders and details. Thanks for contributing an answer to Stack Overflow! Please be sure to answer the question. 06-22-2021 06:04 AM. Remarks. But i need several codes, so I want to use IN formula instead of using "=" condition. We’ll discuss this example again. Power BI Datamart also comes with a unified editor in the Power BI Service. 200. A filter predicate with a simple AND condition between two columns works faster if replaced by two filter arguments, one for each column. Here are the top three that come to mind: This simplest is a lookup function but only works if there is a single category associated with each product in the SearchTable. For the Sales PD scenario, we used the following query to. In that video, there was also a discussion of a Microsoft document that espoused avoiding the use of FILTER as a filter argument. I've just started to learn DAX and I'm reading a number of different books including the Definitive Guide to DAX and had a question about the CALCULATETABLE vs. 10-20-2016 01:08 AM. (*) In Excel 2016 you cannot apply an external filter context (e. returning users or repeat purchase behavior. 10-20-2016 01:08. It's done by passing in filter arguments, which are either Boolean expressions, table expressions, or special filter functions. Note: We can also download Power BI. Hello, I am trying to create a new table from a much larger existing table, with only the filtered rows. The only blank row that is ignored is the one added to a table in case of an invalid relationship. This article explains how to use KEEPFILTERS to intersect instead of overriding an existing filter context in DAX, simplifying the code and improving performance. But don’t expect that CALCULATETABLE() always has the effect of improving efficiency. table. The custom DAX formula reduces the execution time by 20% compared to v1 ( LASTNONBLANK) and by 35% compared to v2 ( LASTNONBLANKVALUE ). Calculatetable dax. I’m not getting the expected output when using CALCULATE. Num Transactions = COUNTROWS ( RELATEDTABLE ( Sales ) ) The result is the number of rows in Sales that are related to each category. g. However, DISTINCTCOUNT is better in that case. Παρατηρήσεις. The ALLSELECTED function gets the context that represents all rows and columns in the query, while keeping explicit filters and contexts other than row and column filters. This approach makes it very easy to apply a filter to the top 10 products in a report, according to the selection required in other slicers or visuals. If you are not familiar with the concept context transition, you may find helpful to read Understanding context transition in DAX. These functions are CALCULATE and CALCULATETABLE. ADDCOLUMNS (. The purpose of this question is to understand the filter context of calculatetable. But other than these, it is a question for Marco if he is lurking around out there. We would use the CALCULATETABLE function when we need to use other functions that expect a table as an expression and CALCULATE when we use functions that expect a single value. This includes both the original row contexts (if any) and the original filter context.