{"id":3568,"date":"2021-09-10T10:06:51","date_gmt":"2021-09-10T17:06:51","guid":{"rendered":"https:\/\/SUMMALAI.COM\/?p=3568"},"modified":"2021-09-10T10:06:53","modified_gmt":"2021-09-10T17:06:53","slug":"power-bi-calculatetable-function-dax","status":"publish","type":"post","link":"https:\/\/SUMMALAI.COM\/?p=3568","title":{"rendered":"Power BI: CALCULATETABLE function DAX"},"content":{"rendered":"\n
CALCULATETABLE function is a power bi filter function in DAX that evaluates a table expression in a context modified by the given filters. It returns a table of values.<\/h5>\n\n\n\n
CALCULATETABLE(<expression>,<filter1>,<filter2>,\u2026)<\/pre>\n\n\n\n
<expression> is a table expression to be evaluated. A measure can not be used as expression.<\/p>\n\n\n\n
<filter1>,<filter2>.. is a Boolean expression or a table expression that defines a filter.<\/p>\n\n\n\n
Lets look at an example of  DAX using CALCULATETABLE  function.<\/p>\n\n\n\n
Here we have sample table named TransactionHistory<\/strong> which contains product details as given below.<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
Lets see the behaviour of CALCULATETABLE function.<\/p>\n\n\n\n
Basic use of CALCULATETABLE function<\/strong><\/p>\n\n\n\n
Lets create a table named calctable<\/strong> to filter a table records for quantity >1 using following DAX.<\/p>\n\n\n\n
CalCtable = CALCULATETABLE(TransactionHistory,TransactionHistory[Quantity] >1)<\/pre>\n\n\n\n
\"\"<\/figure>\n\n\n\n
Once you commit DAX function, a table named as calctable<\/strong> is created in data model.<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
This table is just a copy of actual table TransactionHistory<\/strong> but contains only those products for which a quantity value is greater than1.<\/p>\n\n\n\n
Lets drag a table fields into a table visual, and you can see all the products having a quantity greater than 1.<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
You can also compare both tables result sets, you can clearly see that calctable<\/strong> contains only those products for which quantity value is greater than 1.<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
So, using CALCULATETABLE function you can sort table records based on any specific condition.<\/p>\n\n\n\n
CALULATETABLE function with Summarize function<\/h4>\n\n\n\n
Lets modify the above DAX calculation and returns only specific columns ProductID<\/strong>, Quantity<\/strong>, and TransactionDate<\/strong> only.<\/p>\n\n\n\n
CalCtable =\nCALCULATETABLE (\nSUMMARIZE (\nTransactionHistory,\nTransactionHistory[ProductId],\nTransactionHistory[Quantity],\nTransactionHistory[TransactionDate]\n),\nTransactionHistory[Quantity] > 1\n)<\/pre>\n\n\n\n
\"\"<\/figure>\n\n\n\n
Once you commit the DAX, you will get only those columns that you want to see as shown in below screenshot.<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
Using CALULATETABLE function within measures<\/h4>\n\n\n\n
You can also use CALCULATETABLE function within measure based on requirement so rather than creating a separate filtered table it can be used inside any measure.<\/p>\n\n\n\n
SumofQuantity>1 =\n\nSUMX(\nCALCULATETABLE (\nTransactionHistory,\nTransactionHistory[Quantity] > 1\n),TransactionHistory[Quantity])<\/pre>\n\n\n\n
Now once you commit the DAX, and drag the measure into card visual.<\/p>\n\n\n\n
As you can see, it returns a total quantity sum of products for which quantity is greater than1 that is 21 as shown in below screenshot.<\/p>\n\n\n\n
\"\"
Ref: https:\/\/sqlskull.com\/2020\/06\/09\/calculatetable\/<\/figcaption><\/figure>\n","protected":false},"excerpt":{"rendered":"
CALCULATETABLE function is a power bi filter function in DAX that evaluates a table expression in a context modified by the given filters. It returns a table of values. CALCULATETABLE(<expression>,<filter1>,<filter2>,\u2026) <expression> is a table expression to be evaluated. A measure can not be used as expression. <filter1>,<filter2>.. is a Boolean expression or a table expression Read More<\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_bbp_topic_count":0,"_bbp_reply_count":0,"_bbp_total_topic_count":0,"_bbp_total_reply_count":0,"_bbp_voice_count":0,"_bbp_anonymous_reply_count":0,"_bbp_topic_count_hidden":0,"_bbp_reply_count_hidden":0,"_bbp_forum_subforum_count":0,"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"footnotes":""},"categories":[10,621],"tags":[847],"class_list":["post-3568","post","type-post","status-publish","format-standard","hentry","category-microsoft","category-power-bi","tag-calculatetable"],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/posts\/3568","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=3568"}],"version-history":[{"count":1,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/posts\/3568\/revisions"}],"predecessor-version":[{"id":3569,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/posts\/3568\/revisions\/3569"}],"wp:attachment":[{"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=3568"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=3568"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=3568"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}