{"id":82927,"date":"2019-01-14T18:17:41","date_gmt":"2019-01-14T18:17:41","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=82927"},"modified":"2026-03-09T10:51:13","modified_gmt":"2026-03-09T10:51:13","slug":"using-the-dax-calculate-and-values-functions","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/bi-sql-server\/using-the-dax-calculate-and-values-functions\/","title":{"rendered":"DAX CALCULATE & VALUES Functions Explained"},"content":{"rendered":"\n

The CALCULATE function is the most important function in DAX – it evaluates an expression in a modified filter context, letting you compute values like percentage-of-total, year-over-year comparisons, and filtered aggregations that aren’t possible with basic measures. CALCULATE takes two arguments: the expression to evaluate and one or more filter modifications. Pair it with the VALUES function (which returns the distinct values in a column respecting the current filter context) and the ALL function (which removes filters) to build the majority of business intelligence calculations in Power BI, PowerPivot, or Analysis Services tabular models.<\/p>\n\n\n

The series so far:<\/strong><\/p>\n

    \n
  1. Creating Calculated Columns Using DAX<\/a><\/li>\n
  2. Creating Measures Using DAX<\/a><\/li>\n
  3. Using the DAX Calculate and Values Functions<\/a><\/li>\n
  4. Using the FILTER Function in DAX<\/a><\/li>\n
  5. Cracking DAX\u00a0\u2013 the EARLIER and RANKX Functions<\/a><\/li>\n
  6. Using Calendars and Dates in Power BI<\/a><\/li>\n
  7. Creating Time-Intelligence Functions in DAX<\/span><\/a><\/li>\n<\/ol>\n\n\n\n\n

    If you should ever start reading a book on DAX, you will quickly reach a chapter on the CALCULATE<\/code> function. The book will tell you that the CALCULATE<\/code> function is at the heart of everything that you do in DAX and is the key to understanding the language. A delegate on one of my courses adopted the policy of starting every formula with =CALCULATE<\/code>, and it\u2019s not such a bad approach! This article explains how to use the CALCULATE<\/code> function and also how to use the (almost) equally important VALUES<\/code> function.<\/p>\n\n\n\n

    The Example Database for this Article<\/h2>\n\n\n\n

    This article uses the same simple database as its two predecessors. This database shows sales of three toys for different cities around the world:<\/p>\n\n\n\n

    \"\"<\/figure>\n\n\n\n

    <\/p>\n\n\n\n

    You can import this data into your own Power BI data model by first downloading this Excel workbook<\/a>, or by running this SQL script<\/a> in SQL Server Management Studio.<\/p>\n\n\n\n

    As for the previous articles in this series, everything I describe below will work just as well in Power BI, PowerPivot or Analysis Services (Tabular Model), each of which Wise Owl train<\/a>.<\/p>\n\n\n\n

    The CALCULATE Function<\/h2>\n\n\n\n

    To understand the CALCULATE<\/code> function, you must understand filter context, so that\u2019s where I\u2019ll begin for this article.<\/p>\n\n\n\n

    Read also:<\/strong> Calendar tables in Power BI<\/a><\/p>\n\n\n\n

    Filter Context Explained Using an Excel Pivot Table<\/h3>\n\n\n\n

    Suppose you have the following pivot table in Excel, showing the number of sales for each country, city, and product in the database. The figure selected shows that there were three sales of Timmy Tortoise products in London (UK):<\/p>\n\n\n\n

    \"\"<\/figure>\n\n\n\n

    <\/p>\n\n\n\n

    The filter context for the shaded cell containing the number 3 is therefore as follows:<\/p>\n\n\n\n

    Country dimension: UK<\/p>\n\n\n\n

    City dimension: London<\/p>\n\n\n\n

    Product dimension: Timmy Tortoise<\/p>\n\n\n\n

    If you were to double-click on this cell in Excel, you would see the underlying rows:<\/p>\n\n\n\n

    \"\"<\/figure>\n\n\n\n

    <\/p>\n\n\n\n

    These are the three sales which took place for this product in this country and city.<\/p>\n\n\n\n

    Now suppose that you change your pivot table to show the number of sales as a percentage of the total for each column. This would give:<\/p>\n\n\n\n

    \"\"<\/figure>\n\n\n\n

    <\/p>\n\n\n\n

    The figure for Timmy Tortoise for London is 75%, which is:<\/p>\n\n\n\n

    Total sales for London for Timmy Tortoise \/ \nTotal sales for London for all products<\/pre>\n\n\n\n
    This gives 75% because this is the result you get when you divide 3 (the number of sales in London for Timmy Tortoise) by 4 (the number of sales in London for all products).<\/p>\n\n\n\n
    Note that I\u2019ll often refer in this article to the numerator and denominator. In any fraction A \/ B, the numerator is A and the denominator is B (but you knew that from school maths, didn\u2019t you?).<\/p>\n\n\n\n
    Read also:<\/strong>
    Filtering DAX for paginated reports<\/a>
    Temporary tables in SQL Server<\/a>
    Subqueries in SQL for comparison<\/a><\/p>\n\n\n\n
    Removing One Constraint Using CALCULATE and ALL<\/h3>\n\n\n\n
    Now suppose that you want to recreate this pivot table using a matrix and slicer in Power BI:<\/p>\n\n\n\n
    \"\"<\/figure>\n\n\n\n
    <\/p>\n\n\n\n
    The figures are exactly the same, and for Timmy Tortoise for London you\u2019ll see 75% because this is the ratio between the number of sales for this product and city (3) against the number of sales for all products and this city (4).<\/p>\n\n\n\n
    To solve this problem, you\u2019ll use the CALCULATE<\/code> function which is the answer to most questions in DAX. The syntax of the function is as follows:<\/p>\n\n\n\n
    \"\"<\/figure>\n\n\n\n
    <\/p>\n\n\n\n
    The measure you should create (and show) is this:<\/p>\n\n\n\n
    % of all products = DIVIDE(\n    \/\/ the numerator: number of sales for the current filter context\n    COUNT(Sales[SalesId]),\n    \/\/ the denominator: number of sales for the current filter\n    \/\/ context, but for ALL products\n    CALCULATE(\n        COUNT(Sales[SalesId]),\n        ALL('Product'[ProductName])\n    )\n)<\/pre>\n\n\n\n
    I\u2019ve put my measure in a separate table \u2013 if you\u2019re not sure how to create this table or how to create measures, see the previous article<\/a> in this series. What the measure does is to calculate the numerator (the number of sales for the current product and city) and divide this by the denominator (the number of sales for the current city only, with any product constraint removed). Here\u2019s what this calculates:<\/p>\n\n\n\n
    Total sales for the current filter context \/ \nTotal sales for the current filter context, \nbut removing any product constraint<\/pre>\n\n\n\n
    If you display row and column totals for this measure, you get this:<\/p>\n\n\n\n
    \"\"<\/figure>\n\n\n\n
    <\/p>\n\n\n\n
    The figures in the bottom row make sense: total sales for London for all products divided by total sales for London for all products will always give 100%!<\/p>\n\n\n\n
    Removing Multiple Constraints Using ALL<\/h3>\n\n\n\n
    Suppose that you now want to display the number of sales as a percentage of the total for all cities and for all products, to get this:<\/p>\n\n\n\n
    \"\"<\/figure>\n\n\n\n
    <\/p>\n\n\n\n
    In this case, the numerator is the total number of sales in the UK in London for Timmy Tortoise, and the denominator is the total number of sales in the UK; the other two constraints have been removed from the denominator. Here is a DAX measure to calculate these figures:<\/p>\n\n\n\n
    % of all products and cities = DIVIDE(\n    \/\/ divide the number of sales ...\n    COUNT(Sales[SalesId]),\n    \/\/ ... by the number of sales for all products and\n    \/\/ cities\n    CALCULATE(\n        COUNT(Sales[SalesId]),\n        ALL('Product'[ProductName]),\n        ALL(City[CityName])\n    )\n)<\/pre>\n\n\n\n
    You can use the ALL<\/code> function as many times as you like \u2013 each time it will remove one dimension from the filter context.<\/p>\n\n\n\n
    Using ALLEXCEPT to Remove All but One Constraint<\/h3>\n\n\n\n
    An alternative solution to the above problem would be to calculate this ratio:<\/p>\n\n\n\n
    Total sales for the current filter context \/ \nTotal sales for the current filter context, \nbut removing every constraint apart from the country one<\/pre>\n\n\n\n
    Here\u2019s a quick comparison of the two approaches:<\/p>\n\n\n\n
    \"\"<\/figure>\n\n\n\n
    <\/p>\n\n\n\n
    Here\u2019s a measure which would show each product\/city\u2019s contribution to the grand total for each country:<\/p>\n\n\n\n
    % relaxing everything but country = DIVIDE(\n    \/\/ divide the number of sales ...\n    COUNT(Sales[SalesId]),\n    \/\/ ... by the number of sales, keeping only the \n    \/\/ country constraint\n    CALCULATE(\n        COUNT(Sales[SalesId]),\n        ALLEXCEPT(\n            Sales,\n            Country[CountryName]\n        )\n    )\n)<\/pre>\n\n\n\n
    It\u2019s up to you whether you think it\u2019s more elegant to remove constraints from the filter context individually using ALL<\/code>, or to remove all constraints apart from one using ALLEXCEPT<\/code>.<\/p>\n\n\n\n
    Replacing Filter Context Using CALCULATE<\/h3>\n\n\n\n
    The previous examples have all involved removing the filter context in whole or in part. What if you wanted to change it to show the ratio for each matrix cell between the number of sales for that cell and the number of sales for the same filter context, but for the product Timmy Tortoise? That is, you want to calculate:<\/p>\n\n\n\n
    Total sales for the current filter context \/ \nTotal sales for the current filter context, but ignoring \nany product constraint and using the Timmy Tortoise product instead<\/pre>\n\n\n\n
    \"\"<\/figure>\n\n\n\n
    <\/p>\n\n\n\n
    For this example, it\u2019s inevitable that the figures for Timmy Tortoise should be 100%, because for each cell in this row you\u2019re dividing a figure by itself. The matrix above shows that sales of Olly Owl were only a third of those for Timmy Tortoise in London but were twice those for Timmy Tortoise in Manchester.<\/p>\n\n\n\n
    A formula that you could use might be:<\/p>\n\n\n\n
    % of Timmy = DIVIDE(\n    \/\/ divide the number of sales for the filter context by ...\n    COUNT(Sales[SalesId]),\n    \/\/ ... the number of sales for the filter context, but\n    \/\/ removing any product constraint and replacing this \n    \/\/ with a constraint that the product should equal Timmy Tortoise\n    CALCULATE(\n        COUNT(Sales[SalesId]),\n        'Product'[ProductName] = \"Timmy tortoise\"\n    )\n)<\/pre>\n\n\n\n
    What this does is to calculate the number of sales for a particular country, city and product, and divide this by the number of sales for the same country and city, but for Timmy Tortoise<\/em>. The extra filter you add in the CALCULATE<\/code> formula doesn\u2019t build on the filter context for the product, but instead replaces it.<\/p>\n\n\n\n
    Using the ALLSELECTED Function as Opposed to ALL<\/h3>\n\n\n\n
    Sometimes you\u2019ll want to reference just the selected items in a dimension, in a slicer, for example, rather than include all of the items in your formula. Here\u2019s an example of a matrix where you might want to do this:<\/p>\n\n\n\n
    \"\"<\/figure>\n\n\n\n
    <\/p>\n\n\n\n
    The measure shown initially is as follows:<\/p>\n\n\n\n
    % of all sales = DIVIDE(\n    \/\/ divide number of sales for filter context ...\n    COUNT(Sales[SalesId]),\n    \/\/ ... number of sales for all countries\n    CALCULATE(\n        COUNT(Sales[SalesId]),\n        ALL(Country[CountryName])\n    )\n)<\/pre>\n\n\n\n
    The figures don\u2019t add up to 100% because for each country the statistic shown equals:<\/p>\n\n\n\n
    the number of sales for that country \/ \nthe number of sales for all countries<\/pre>\n\n\n\n
    In this example the USA is included in the denominator but not in the numerator. To get the statistic to work, you need to reference only the selected countries in the denominator:<\/p>\n\n\n\n
    % of selected country sales = DIVIDE(\n    \/\/ take the number of sales for each country\n    COUNT(Sales[SalesId]),\n    \/\/ divide this by the number of sales for all \n    \/\/ currently selected countries\n    CALCULATE(\n        COUNT(Sales[SalesId]),\n        ALLSELECTED(Country[CountryName])\n    )\n)<\/pre>\n\n\n\n
    This gives the required 100% total, regardless of the combination of countries you select in the slicer:<\/p>\n\n\n\n
    \"\"<\/figure>\n\n\n\n
    <\/p>\n\n\n\n
    Context Transition Using the CALCULATE Function<\/h3>\n\n\n\n
    Before moving on from the CALCULATE<\/code> function, it has one more string to its bow. Consider the following two formulae:<\/p>\n\n\n\n
    Total sales A = SUMX(Sales,[Price]*[Quantity])\nTotal sales B = CALCULATE(SUMX(Sales,[Price]*[Quantity]))<\/pre>\n\n\n\n
    If you\u2019ve been following up to now, you\u2019ll realise that these two formulae must give the same result under all circumstances:<\/p>\n\n\n\n
    The first formula gives the total sales value for the current filter context;<\/p>\n\n\n\n
    The second formula gives the total sales value for the current filter context, with no extra modifications to it.<\/p>\n\n\n\n
    However \u2026 what happens if there isn\u2019t a filter context to begin with? In this case the second formula will create a filter context, and hence return a different answer than the first. How can you not have a filter context? By creating a calculated column in a table:<\/p>\n\n\n\n
    \"\"<\/figure>\n\n\n\n
    <\/p>\n\n\n\n
    The first formula gives the same result for each row. Because calculated columns don\u2019t have a filter context by default, the formula sums sales over all of the rows in the sales table, giving the same answer (238.32) for each.<\/p>\n\n\n\n
    Remember that the second formula is as follows:<\/p>\n\n\n\n
    Total sales B = CALCULATE(SUMX(Sales,[Price]*[Quantity]))<\/pre>\n\n\n\n
    The CALCULATE<\/code> function doesn\u2019t just allow you to change the filter context, it can create it, too. For each country, this creates a filter context, limiting the rows in the sales table to those for the country in question, and hence giving a different answer for each row of the above table. The process of changing row context into filter context in this way is called context transition<\/em>.<\/p>\n\n\n\n
    The VALUES Function<\/h2>\n\n\n\n
    Learning the CALCULATE<\/code> function is key to understanding how to create measures in DAX, but the VALUES<\/code> function runs it a close second. The rest of this article shows what this function does, and how to use it to create a range of effects in your Power BI reports.<\/p>\n\n\n\n
    What the VALUES function returns<\/h3>\n\n\n\n
    The VALUES<\/code> function returns the table of data for the current filter context. To explain what this sentence means, here\u2019s an example. Suppose you create this table in a Power BI report:<\/p>\n\n\n\n
    \"\"<\/figure>\n\n\n\n
    <\/p>\n\n\n\n
    Note for this example that I\u2019ve used a filter (not shown here) on the report to avoid showing any blank countries). The Number of cities<\/em> column shows the number of cities for each country, using the following measure:<\/p>\n\n\n\n
    Number of cities = COUNTROWS(VALUES(City[CityName]))<\/pre>\n\n\n\n
    If you could look at the filter context, this is what you would see:<\/p>\n\n\n\n
    \"\"<\/figure>\n\n\n\n
    <\/p>\n\n\n\n
    The VALUES<\/code> function allows you to return a table containing one or more of the columns in the current filter context\u2019s underlying table. For example, you could create this measure:<\/p>\n\n\n\n
    Cities = VALUES(City[CityName])<\/pre>\n\n\n\n
    If you display this measure in your Power BI report, you\u2019ll get this error message:<\/p>\n\n\n\n
    \"\"<\/figure>\n\n\n\n
    <\/p>\n\n\n\n
    The problem is that you\u2019re trying to display a column of values in a single cell. This would work for Brazil and China, each of which only has one city, but wouldn\u2019t work for the other three countries.<\/p>\n\n\n\n
    What you could do, however, is to test whether there is only one city for a country, and in this event show its name; otherwise, you could show a message saying that there are multiple cities. Here\u2019s a measure to do this:<\/p>\n\n\n\n
    Cities = IF(\n    \/\/ if there is  one city for the current \n    \/\/ filter context ...\n    COUNTROWS(VALUES(City[CityName])) = 1,\n    \/\/ ... shows the city's name\n    VALUES(City[CityName]),\n    \/\/ Otherwise, show a message\n    \"More than one city\"\n)<\/pre>\n\n\n\n
    Displaying this measure in our report would give:<\/p>\n\n\n\n
    \"\"<\/figure>\n\n\n\n
    <\/p>\n\n\n\n
    For the total row there are lots of cities in the current filter context, so naturally you get the More than one city<\/em> message.<\/p>\n\n\n\n
    The above example shows two important features of the VALUES<\/code> function. The first is that it returns a table of data. In the measure above, the COUNTROWS<\/code> function expects to receive a table:<\/p>\n\n\n\n
    \"\"<\/figure>\n\n\n\n
    <\/p>\n\n\n\n
    Fortunately, that\u2019s what\u2019s supplied:<\/p>\n\n\n\n
    \"\"<\/figure>\n\n\n\n
    <\/p>\n\n\n\n
    The VALUES<\/code> function in this case returns a single-column table which looks like this for each of the 5 countries:<\/p>\n\n\n\n
    \"\"<\/figure>\n\n\n\n
    <\/p>\n\n\n\n
    The second important point to understand about the VALUES<\/code> function is that you can\u2019t put a table into a cell without performing some sort of aggregation on it first, since a table can potentially contain multiple values. What this means is that the selected part of the measure below shouldn\u2019t work:<\/p>\n\n\n\n
    \"\"<\/figure>\n\n\n\n
    <\/p>\n\n\n\n
    This is because the VALUES<\/code> function returns a column of data, and even though you know that there is only one row in this column, and hence only one value, you would normally still need to apply some aggregation function (e.g., MAX<\/code>, MIN<\/code>, SUM<\/code>) to the data.<\/p>\n\n\n\n
    Happily, there is one exception to this rule. If a call to the VALUES<\/code> function returns a table with one column and one row, you can automatically treat this as a single scalar value without any additional work. This is why this measure works!<\/p>\n\n\n\n
    The HASONEVALUE Function and Other Alternatives<\/h3>\n\n\n\n
    Checking whether the filter context only contains one value for a particular column is a common thing to do. It\u2019s so common, in fact, that DAX has a dedicated function called HASONEVALUE<\/code> to do this.<\/p>\n\n\n\n
    You could rewrite the measure like this:<\/p>\n\n\n\n
    Cities = IF(\n    \/\/ if there is  one city for the current \n    \/\/ filter context ...\n    HASONEVALUE(City[CityName]),\n    \/\/ ... shows the city's name\n    VALUES(City[CityName]),\n    \/\/ Otherwise, show a message\n    \"More than one city\"\n)<\/pre>\n\n\n\n
    Another solution would be to count how many distinct city names there are in the current filter context:<\/p>\n\n\n\n
    Cities = IF(\n    \/\/ if there is  one city for the current \n    \/\/ filter context ...\n    DISTINCTCOUNT(City[CityName]) = 1,\n    \/\/ ... shows the city's name\n    VALUES(City[CityName]),\n    \/\/ Otherwise, show a message\n    \"More than one city\"\n)<\/pre>\n\n\n\n
    These three methods, using VALUES<\/code>, HASONEVALUE<\/code> or DISTINCTCOUNT<\/code>, are interchangeable, and I don\u2019t think there\u2019s any clear reason to favour one over another.<\/p>\n\n\n\n
    Using CONCATENATEX to List Out Multiple Values<\/h3>\n\n\n\n
    For this example, you might want to list out the names of the cities for each country. You can do this using the CONCATENATEX<\/code> function, which has this syntax:<\/p>\n\n\n\n
    \"\"<\/figure>\n\n\n\n
    <\/p>\n\n\n\n
    The arguments to this function are thus:<\/p>\n\n\n
    \n