{"id":1789,"date":"2020-10-09T08:24:07","date_gmt":"2020-10-09T08:24:07","guid":{"rendered":"https:\/\/bi.unija.com\/en\/?p=1789"},"modified":"2020-10-22T11:48:18","modified_gmt":"2020-10-22T11:48:18","slug":"x-functions-in-dax","status":"publish","type":"post","link":"https:\/\/bi.unija.com\/en\/x-functions-in-dax\/","title":{"rendered":"X-functions in DAX"},"content":{"rendered":"

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

X-functions in DAX are iterator functions. This means they are aware of rows. For example, function SUMX() calculates the sum of expression for each row separately. You can find more on row context in the article Row Context in Power BI and Context Transition in Power BI. Let’s look at the the most common x-functions.<\/p>\n

SUMX()<\/h1>\n

Calculates the value of the expression for each row and returns the sum of all the rows.<\/p>\n

Syntax<\/h2>\n

SUMX(Table, Expression)<\/p>\n\n\n\n\n\n
Parameter<\/td>\nDescription<\/td>\n<\/tr>\n
Table<\/td>\nName of the table<\/td>\n<\/tr>\n
Expression<\/td>\nExpression that is calculated for each row<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

Example<\/h2>\n

Table of sold products Sales<\/strong> with fields Price<\/strong> and Quantity<\/strong>.<\/p>\n

<\/p>\n

We want to calculate total income from sold products. We can’t use SUM()<\/strong>, we have to calculate the product for each row separately and then sum the rows into final result. That is what SUMX()<\/strong> does.<\/p>\n

SUMX_Example = SUMX(Sales, Sales[Price]*Sales[Quantity])<\/p>\n

For mathematically fluent readers: SUMX()<\/strong> acts like dot product in this case.<\/p>\n

AVERAGEX()<\/h1>\n

Calculates the value of the expression for each row and returns the average of all the rows.<\/p>\n

Syntax<\/h2>\n

AVERAGEX (Table, Expression)<\/p>\n\n\n\n\n\n
Parameter<\/td>\nDescription<\/td>\n<\/tr>\n
Table<\/td>\nName of the table<\/td>\n<\/tr>\n
Expression<\/td>\nExpression, evaluated for each row<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

Example<\/h2>\n

AVERAGEX_Example = AVERAGEX(Sales, Sales[Price]*Sales[Quantity])<\/p>\n

Example calculates the product of Price<\/strong> and Quantity<\/strong> column in Sales<\/strong> table for each row and returns the average of all the rows.<\/p>\n

MINX()<\/h1>\n

Calculates the value of the expression for each row and returns the lowest value.<\/p>\n

Syntax<\/h2>\n

MINX(Table, Expression)<\/p>\n\n\n\n\n\n
Parameter<\/td>\nDescription<\/td>\n<\/tr>\n
Table<\/td>\nName of the table<\/td>\n<\/tr>\n
Expression<\/td>\nExpression, evaluated for each row<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

Example<\/h2>\n

MINX_Example = MINX(Sales, Sales[Price]*Sales[Quantity])<\/p>\n

Example calculates the product of Price<\/strong> and Quantity<\/strong> column in Sales<\/strong> table for each row and returns the minimum of all values.<\/p>\n

MAXX()<\/h1>\n

Calculates the value of the expression for each row and returns the highest value.<\/p>\n

Syntax<\/h2>\n

MAXX(Table, Expression)<\/p>\n\n\n\n\n\n
Parameter<\/td>\nDescription<\/td>\n<\/tr>\n
Table<\/td>\nName of the table<\/td>\n<\/tr>\n
Expression<\/td>\nExpression, evaluated for each row<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

Example<\/h2>\n

MAXX_Example = MAXX(Sales, Sales[Price]*Sales[Quantity])<\/p>\n

Example calculates the product of Price<\/strong> and Quantity<\/strong> column in Sales<\/strong> table for each row and returns the maximum of all values.<\/p>\n

COUNTX()<\/h1>\n

Returns the number of non-empty rows in a column or number of non-empty result of the expression.<\/p>\n

Syntax<\/h2>\n

COUNTX(Table, Expression)<\/p>\n\n\n\n\n\n
Parameter<\/td>\nDescription<\/td>\n<\/tr>\n
Table<\/td>\nName of the table<\/td>\n<\/tr>\n
Expression<\/td>\nExpression, evaluated for each row<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

Example<\/h2>\n

COUNTX_Example = COUNT(Sales, Sales[Price]*Sales[Quantity])<\/p>\n

Example calculates the product of Price<\/strong> and Quantity<\/strong> column in Sales<\/strong> table for each row and returns the number of non-empty results.<\/p>\n

CONCATENATEX ()<\/h1>\n

Calculates the value of expression for each row and returns concatenated values.<\/p>\n

Syntax<\/h2>\n

CONCATENATEX (Table, Expression, [Delimiter])<\/p>\n\n\n\n\n\n\n
Parameter<\/td>\nDescription<\/td>\n<\/tr>\n
Table<\/td>\nName of the table<\/td>\n<\/tr>\n
Expression<\/td>\nExpression, evaluated for each row<\/td>\n<\/tr>\n
Delimiter<\/td>\nDelimiter used to separate expression (optional)<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

Example<\/h2>\n

Table Customers<\/strong> contains columns FirstName <\/strong>and LastName<\/strong>.<\/p>\n

<\/p>\n

We use CONCATENATEX() <\/strong>function to get a list of all the customers.<\/p>\n

CONCATENATEX_Example = CONCATENATEX(Customers, [FirstName] & ” ” & [LastName], “,”)<\/p>\n

Function returns “Craig Ferguson, James May”.<\/p>\n

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

For Power BI version 2.76.5678.782 (December 2019). X-functions in DAX are iterator functions. This means they are aware of rows. For example, function SUMX() calculates the sum of expression for each row separately. You can find more on row context<\/p>\n","protected":false},"author":5,"featured_media":1792,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[3],"tags":[5,8],"class_list":["post-1789","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-intermediate","tag-dax","tag-power-bi"],"acf":[],"yoast_head":"\nX-functions in DAX - 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\/x-functions-in-dax\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"X-functions in DAX - Bi Unija\" \/>\n<meta property=\"og:description\" content=\"For Power BI version 2.76.5678.782 (December 2019). X-functions in DAX are iterator functions. This means they are aware of rows. For example, function SUMX() calculates the sum of expression for each row separately. You can find more on row context\" \/>\n<meta property=\"og:url\" content=\"https:\/\/bi.unija.com\/en\/x-functions-in-dax\/\" \/>\n<meta property=\"og:site_name\" content=\"Bi Unija\" \/>\n<meta property=\"article:published_time\" content=\"2020-10-09T08:24:07+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2020-10-22T11:48:18+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/bi.unija.com\/en\/wp-content\/uploads\/sites\/2\/2020\/10\/28.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\/x-functions-in-dax\/\",\"url\":\"https:\/\/bi.unija.com\/en\/x-functions-in-dax\/\",\"name\":\"X-functions in DAX - Bi Unija\",\"isPartOf\":{\"@id\":\"https:\/\/bi.unija.com\/en\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/bi.unija.com\/en\/x-functions-in-dax\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/bi.unija.com\/en\/x-functions-in-dax\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/bi.unija.com\/en\/wp-content\/uploads\/sites\/2\/2020\/10\/28.png\",\"datePublished\":\"2020-10-09T08:24:07+00:00\",\"dateModified\":\"2020-10-22T11:48:18+00:00\",\"author\":{\"@id\":\"https:\/\/bi.unija.com\/en\/#\/schema\/person\/3bed36623727c7162e421c4366a54e10\"},\"breadcrumb\":{\"@id\":\"https:\/\/bi.unija.com\/en\/x-functions-in-dax\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/bi.unija.com\/en\/x-functions-in-dax\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/bi.unija.com\/en\/x-functions-in-dax\/#primaryimage\",\"url\":\"https:\/\/bi.unija.com\/en\/wp-content\/uploads\/sites\/2\/2020\/10\/28.png\",\"contentUrl\":\"https:\/\/bi.unija.com\/en\/wp-content\/uploads\/sites\/2\/2020\/10\/28.png\",\"width\":720,\"height\":480},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/bi.unija.com\/en\/x-functions-in-dax\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/bi.unija.com\/en\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"X-functions in DAX\"}]},{\"@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":"X-functions in DAX - 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\/x-functions-in-dax\/","og_locale":"en_US","og_type":"article","og_title":"X-functions in DAX - Bi Unija","og_description":"For Power BI version 2.76.5678.782 (December 2019). X-functions in DAX are iterator functions. This means they are aware of rows. For example, function SUMX() calculates the sum of expression for each row separately. You can find more on row context","og_url":"https:\/\/bi.unija.com\/en\/x-functions-in-dax\/","og_site_name":"Bi Unija","article_published_time":"2020-10-09T08:24:07+00:00","article_modified_time":"2020-10-22T11:48:18+00:00","og_image":[{"width":720,"height":480,"url":"https:\/\/bi.unija.com\/en\/wp-content\/uploads\/sites\/2\/2020\/10\/28.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\/x-functions-in-dax\/","url":"https:\/\/bi.unija.com\/en\/x-functions-in-dax\/","name":"X-functions in DAX - Bi Unija","isPartOf":{"@id":"https:\/\/bi.unija.com\/en\/#website"},"primaryImageOfPage":{"@id":"https:\/\/bi.unija.com\/en\/x-functions-in-dax\/#primaryimage"},"image":{"@id":"https:\/\/bi.unija.com\/en\/x-functions-in-dax\/#primaryimage"},"thumbnailUrl":"https:\/\/bi.unija.com\/en\/wp-content\/uploads\/sites\/2\/2020\/10\/28.png","datePublished":"2020-10-09T08:24:07+00:00","dateModified":"2020-10-22T11:48:18+00:00","author":{"@id":"https:\/\/bi.unija.com\/en\/#\/schema\/person\/3bed36623727c7162e421c4366a54e10"},"breadcrumb":{"@id":"https:\/\/bi.unija.com\/en\/x-functions-in-dax\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/bi.unija.com\/en\/x-functions-in-dax\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/bi.unija.com\/en\/x-functions-in-dax\/#primaryimage","url":"https:\/\/bi.unija.com\/en\/wp-content\/uploads\/sites\/2\/2020\/10\/28.png","contentUrl":"https:\/\/bi.unija.com\/en\/wp-content\/uploads\/sites\/2\/2020\/10\/28.png","width":720,"height":480},{"@type":"BreadcrumbList","@id":"https:\/\/bi.unija.com\/en\/x-functions-in-dax\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/bi.unija.com\/en\/"},{"@type":"ListItem","position":2,"name":"X-functions in DAX"}]},{"@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\/1789"}],"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=1789"}],"version-history":[{"count":2,"href":"https:\/\/bi.unija.com\/en\/wp-json\/wp\/v2\/posts\/1789\/revisions"}],"predecessor-version":[{"id":1794,"href":"https:\/\/bi.unija.com\/en\/wp-json\/wp\/v2\/posts\/1789\/revisions\/1794"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/bi.unija.com\/en\/wp-json\/wp\/v2\/media\/1792"}],"wp:attachment":[{"href":"https:\/\/bi.unija.com\/en\/wp-json\/wp\/v2\/media?parent=1789"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/bi.unija.com\/en\/wp-json\/wp\/v2\/categories?post=1789"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/bi.unija.com\/en\/wp-json\/wp\/v2\/tags?post=1789"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}