{"id":1796,"date":"2020-10-23T05:53:58","date_gmt":"2020-10-23T05:53:58","guid":{"rendered":"https:\/\/bi.unija.com\/en\/?p=1796"},"modified":"2020-10-30T10:26:27","modified_gmt":"2020-10-30T10:26:27","slug":"dax-function-calculate","status":"publish","type":"post","link":"https:\/\/bi.unija.com\/en\/dax-function-calculate\/","title":{"rendered":"DAX function CALCULATE"},"content":{"rendered":"

For Power BI version 2.76.5678.782 (December 2019).<\/em><\/p>\n

CALCULATE()<\/strong> function is the most important function in DAX language. It’s almost the only function that can change the filter context coming from visualizations. You can read more on filter context in articles Filter Context in Power BI<\/a> and Context Transition in Power BI<\/a>.<\/p>\n

So how does CALCULATE()<\/strong> function work? Let’s suppose we make an order of office supplies every month.<\/p>\n

Standard order of supplies contains: paper, pens, folders, envelopes and paper clips.<\/p>\n

While making an order we realize we still have paper clips in storage, so we don’t need to order more. When making an order we say: “Standard order please, without the paper clips”. This is similar to what CALCULATE()<\/strong> function does. It filters the table and calculates the result. We can use none, one or more filters. Functions changes the filter context, which means filters propagate through connections and filter connected tables, all before evaluating and returning the result.<\/p>\n

Syntax<\/h1>\n

CALCULATE(Expression, Filter1, Filter2, Filter3, \u2026)<\/p>\n\n\n\n\n\n
Parameter<\/td>\nDescription<\/td>\n<\/tr>\n
Expression<\/td>\nExpression to be filtered<\/td>\n<\/tr>\n
Filter1, Filter2, Filter3, \u2026<\/td>\nFilters<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

Example: filtering one table<\/h1>\n

Supplies<\/strong> table contains the quantity of standard order of office supplies.<\/p>\n

<\/p>\n

We’d like to know how many pieces of office supplies we’ll order if we know we’ll order everything except for paper clips. We follow the steps bellow.<\/p>\n

1. We create a measure using the formula below.<\/p>\n

CALCULATE_Example = CALCULATE(SUM(Supplies[Quantity]), Supplies[Name] <> “Paper clips”)<\/p>\n

2. We use the measure in Matrix visualization. The answer is – as expected – 1300.<\/p>\n

<\/p>\n

Example: filtering second table<\/h1>\n

Filters can also reference other tables. For example, we want to see the income of products in the Board games<\/strong> group.<\/p>\n

We use the following example:<\/p>\n

CALCULATE_Example = CALCULATE(SUM(Sales[Amount]), Product[Genre] = “Board games”)<\/p>\n

Formula is executed in following steps.<\/p>\n

    \n
  1. Table Products<\/strong> is filtered, leaving rows that have Board games<\/strong> value in column Genre<\/strong>.<\/li>\n
  2. Filter then propagates to the connected table Sales, also leaving only the rows with Board games<\/strong> value in column Genre.<\/strong><\/li>\n
  3. Sum of the Amount<\/strong> column is calculated in the filtered Sales<\/strong> table.<\/li>\n<\/ol>\n

    Example: using multiple filters<\/h1>\n

    We can use any number of filters. For example, we want to see income of products in the Board games <\/strong>group in UK,<\/strong> in year 2019<\/strong>.<\/p>\n

    We use the following formula:<\/p>\n

    CALCULATE_Example = CALCULATE(SUM(Sales[Amount]), Product[Genre] = “Board games”, Location[Name] = “UK”, Calendar[Year] = 2019)<\/p>\n

    Formula is executed in following steps.<\/p>\n

      \n
    1. Table Products<\/strong> is filtered, leaving rows that have value Board games<\/strong> in column Genre<\/strong>.<\/li>\n
    2. Table Location<\/strong> is filtered, leaving rows that have value UK<\/strong> in column Name<\/strong>.<\/li>\n
    3. Table Calendar<\/strong> is filtered, leaving rows that have value 2019<\/strong> in column Year<\/strong>.<\/li>\n
    4. Filters than propagate to the Sales<\/strong> table, leaving only rows that weren’t filtered out.<\/li>\n
    5. Sum of the Amount<\/strong> column is calculated in the filtered Sales<\/strong> table.<\/li>\n<\/ol>\n

       <\/p>\n","protected":false},"excerpt":{"rendered":"

      For Power BI version 2.76.5678.782 (December 2019). CALCULATE() function is the most important function in DAX language. It’s almost the only function that can change the filter context coming from visualizations. You can read more on filter context in articles<\/p>\n","protected":false},"author":5,"featured_media":1800,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[3],"tags":[5,8],"class_list":["post-1796","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-intermediate","tag-dax","tag-power-bi"],"acf":[],"yoast_head":"\nDAX function CALCULATE - Bi Unija<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/bi.unija.com\/en\/dax-function-calculate\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"DAX function CALCULATE - Bi Unija\" \/>\n<meta property=\"og:description\" content=\"For Power BI version 2.76.5678.782 (December 2019). CALCULATE() function is the most important function in DAX language. It’s almost the only function that can change the filter context coming from visualizations. You can read more on filter context in articles\" \/>\n<meta property=\"og:url\" content=\"https:\/\/bi.unija.com\/en\/dax-function-calculate\/\" \/>\n<meta property=\"og:site_name\" content=\"Bi Unija\" \/>\n<meta property=\"article:published_time\" content=\"2020-10-23T05:53:58+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2020-10-30T10:26:27+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/bi.unija.com\/en\/wp-content\/uploads\/sites\/2\/2020\/10\/29.png\" \/>\n\t<meta property=\"og:image:width\" content=\"720\" \/>\n\t<meta property=\"og:image:height\" content=\"480\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/png\" \/>\n<meta name=\"author\" content=\"Branka Trifunovi\u0107\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Branka Trifunovi\u0107\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"2 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/bi.unija.com\/en\/dax-function-calculate\/\",\"url\":\"https:\/\/bi.unija.com\/en\/dax-function-calculate\/\",\"name\":\"DAX function CALCULATE - Bi Unija\",\"isPartOf\":{\"@id\":\"https:\/\/bi.unija.com\/en\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/bi.unija.com\/en\/dax-function-calculate\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/bi.unija.com\/en\/dax-function-calculate\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/bi.unija.com\/en\/wp-content\/uploads\/sites\/2\/2020\/10\/29.png\",\"datePublished\":\"2020-10-23T05:53:58+00:00\",\"dateModified\":\"2020-10-30T10:26:27+00:00\",\"author\":{\"@id\":\"https:\/\/bi.unija.com\/en\/#\/schema\/person\/3bed36623727c7162e421c4366a54e10\"},\"breadcrumb\":{\"@id\":\"https:\/\/bi.unija.com\/en\/dax-function-calculate\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/bi.unija.com\/en\/dax-function-calculate\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/bi.unija.com\/en\/dax-function-calculate\/#primaryimage\",\"url\":\"https:\/\/bi.unija.com\/en\/wp-content\/uploads\/sites\/2\/2020\/10\/29.png\",\"contentUrl\":\"https:\/\/bi.unija.com\/en\/wp-content\/uploads\/sites\/2\/2020\/10\/29.png\",\"width\":720,\"height\":480},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/bi.unija.com\/en\/dax-function-calculate\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/bi.unija.com\/en\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"DAX function CALCULATE\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/bi.unija.com\/en\/#website\",\"url\":\"https:\/\/bi.unija.com\/en\/\",\"name\":\"Bi Unija\",\"description\":\"Just another Bi site\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/bi.unija.com\/en\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/bi.unija.com\/en\/#\/schema\/person\/3bed36623727c7162e421c4366a54e10\",\"name\":\"Branka Trifunovi\u0107\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/bi.unija.com\/en\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/ec99f651bcc98a163161515ec254b974?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/ec99f651bcc98a163161515ec254b974?s=96&d=mm&r=g\",\"caption\":\"Branka Trifunovi\u0107\"},\"url\":\"https:\/\/bi.unija.com\/en\/author\/branka\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"DAX function CALCULATE - Bi Unija","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/bi.unija.com\/en\/dax-function-calculate\/","og_locale":"en_US","og_type":"article","og_title":"DAX function CALCULATE - Bi Unija","og_description":"For Power BI version 2.76.5678.782 (December 2019). CALCULATE() function is the most important function in DAX language. It’s almost the only function that can change the filter context coming from visualizations. You can read more on filter context in articles","og_url":"https:\/\/bi.unija.com\/en\/dax-function-calculate\/","og_site_name":"Bi Unija","article_published_time":"2020-10-23T05:53:58+00:00","article_modified_time":"2020-10-30T10:26:27+00:00","og_image":[{"width":720,"height":480,"url":"https:\/\/bi.unija.com\/en\/wp-content\/uploads\/sites\/2\/2020\/10\/29.png","type":"image\/png"}],"author":"Branka Trifunovi\u0107","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Branka Trifunovi\u0107","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/bi.unija.com\/en\/dax-function-calculate\/","url":"https:\/\/bi.unija.com\/en\/dax-function-calculate\/","name":"DAX function CALCULATE - Bi Unija","isPartOf":{"@id":"https:\/\/bi.unija.com\/en\/#website"},"primaryImageOfPage":{"@id":"https:\/\/bi.unija.com\/en\/dax-function-calculate\/#primaryimage"},"image":{"@id":"https:\/\/bi.unija.com\/en\/dax-function-calculate\/#primaryimage"},"thumbnailUrl":"https:\/\/bi.unija.com\/en\/wp-content\/uploads\/sites\/2\/2020\/10\/29.png","datePublished":"2020-10-23T05:53:58+00:00","dateModified":"2020-10-30T10:26:27+00:00","author":{"@id":"https:\/\/bi.unija.com\/en\/#\/schema\/person\/3bed36623727c7162e421c4366a54e10"},"breadcrumb":{"@id":"https:\/\/bi.unija.com\/en\/dax-function-calculate\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/bi.unija.com\/en\/dax-function-calculate\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/bi.unija.com\/en\/dax-function-calculate\/#primaryimage","url":"https:\/\/bi.unija.com\/en\/wp-content\/uploads\/sites\/2\/2020\/10\/29.png","contentUrl":"https:\/\/bi.unija.com\/en\/wp-content\/uploads\/sites\/2\/2020\/10\/29.png","width":720,"height":480},{"@type":"BreadcrumbList","@id":"https:\/\/bi.unija.com\/en\/dax-function-calculate\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/bi.unija.com\/en\/"},{"@type":"ListItem","position":2,"name":"DAX function CALCULATE"}]},{"@type":"WebSite","@id":"https:\/\/bi.unija.com\/en\/#website","url":"https:\/\/bi.unija.com\/en\/","name":"Bi Unija","description":"Just another Bi site","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/bi.unija.com\/en\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/bi.unija.com\/en\/#\/schema\/person\/3bed36623727c7162e421c4366a54e10","name":"Branka Trifunovi\u0107","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/bi.unija.com\/en\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/ec99f651bcc98a163161515ec254b974?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/ec99f651bcc98a163161515ec254b974?s=96&d=mm&r=g","caption":"Branka Trifunovi\u0107"},"url":"https:\/\/bi.unija.com\/en\/author\/branka\/"}]}},"_links":{"self":[{"href":"https:\/\/bi.unija.com\/en\/wp-json\/wp\/v2\/posts\/1796"}],"collection":[{"href":"https:\/\/bi.unija.com\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/bi.unija.com\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/bi.unija.com\/en\/wp-json\/wp\/v2\/users\/5"}],"replies":[{"embeddable":true,"href":"https:\/\/bi.unija.com\/en\/wp-json\/wp\/v2\/comments?post=1796"}],"version-history":[{"count":6,"href":"https:\/\/bi.unija.com\/en\/wp-json\/wp\/v2\/posts\/1796\/revisions"}],"predecessor-version":[{"id":1814,"href":"https:\/\/bi.unija.com\/en\/wp-json\/wp\/v2\/posts\/1796\/revisions\/1814"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/bi.unija.com\/en\/wp-json\/wp\/v2\/media\/1800"}],"wp:attachment":[{"href":"https:\/\/bi.unija.com\/en\/wp-json\/wp\/v2\/media?parent=1796"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/bi.unija.com\/en\/wp-json\/wp\/v2\/categories?post=1796"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/bi.unija.com\/en\/wp-json\/wp\/v2\/tags?post=1796"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}