CALCULATETABLE()<\/code> function:<\/p>\n\n\n\nEVALUATE\n\nCALCULATETABLE\n(\nSELECTCOLUMNS\n(\nFactSales\n,\"Client\", RELATED(DimCLient[ClientName])\n,\"City\", RELATED(DimGeography[Town])\n,\"Credit Risk\", RELATED(DimCLient[IsCreditRisk])\n,\"Country\", RELATED(DimGeography[CountryName])\n,\"Make\", RELATED(DimVehicle[Make])\n)\n,DimGeography[CountryName] = \"France\"\n,DimVehicle[Make] = \"Ferrari\"\n,DimCLient[IsCreditRisk] = 0\n)<\/pre>\n\n\n\nThe output is as shown in the following image:<\/p>\n\n\n\n<\/p>\n\n\n\n
You need to note a few key complexities if you ever adopt this approach:<\/p>\n\n\n
\n
\nYou will need a clear understanding of the underlying data model in order to use RELATED()<\/code> if you are using SELECTCOLUMNS()<\/code><\/li>\n\n\n\nThere are many subtleties and variations on the theme of filtering data \u2013 some ideas and suggestions are given above (and in the previous article).<\/li>\n\n\n\n AND<\/code> logic for filtering is easy when filtering this way. OR logic can be harder (and is explained in greater detail in the third and final article in this series).<\/li>\n<\/ul>\n<\/div>\n\n\nOverall, this technique is best suited to simple list output. Also, it can get complex when using measures (given the intricacies of DAX context), so for this reason too is probably best used for relatively simple list output.<\/p>\n\n\n\n
Filter tables in CALCULATETABLE()<\/h2>\n\n\n\n CALCULATETABLE()<\/code> can also be used with filter tables as well as (or instead of) simple filter elements of the kind that you just saw.<\/p>\n\n\n\nThe following DAX extract creates a table variable of a list of country names and then uses the table variable as a filter element that is applied by CALCULATETABLE()<\/code>:<\/p>\n\n\n\nDEFINE\nVAR FilterTable = \nSUMMARIZE\n(\n FILTER\n (\n DimGeography, DimGeography[CountryName] in {\"France\", \"Belgium\"}\n )\n,DimGeography[CountryName]\n) \nEVALUATE\nCALCULATETABLE(\nSUMMARIZECOLUMNS(\n DimCLient[ClientName]\n,DimGeography[CountryName]\n,DimGeography[Town]\n,\"Sales\" , SUM(FactSales[SalePrice])\n)\n,FilterTable\n)<\/pre>\n\n\n\nThe output (shortened once again) is as shown in the following image:<\/p>\n\n\n\n<\/p>\n\n\n\n
In practice, this kind of approach can be more useful if the filter table is based on, say, a calculation. As an example, the following DAX sample creates a filter table that dynamically calculates the top two selling makes of car (by sales) and filters on the result:<\/p>\n\n\n\n
DEFINE\nVAR TopSellersTable = \n TOPN(\n 2,\n SUMMARIZECOLUMNS\n ( \n DimVehicle[Make]\n ,\"Total Sales\", SUM(FactSales[SalePrice])\n ),\n [Total Sales]\n ,DESC\n )\nEVALUATE\nCALCULATETABLE(\nSUMMARIZECOLUMNS(\n DimCLient[ClientName]\n,DimGeography[Town]\n,DimVehicle[Make]\n,\"Sales\" , SUM(FactSales[SalePrice])\n)\n,TopSellersTable\n)<\/pre>\n\n\n\nPart of the output from this code snippet is as shown in the following image:<\/p>\n\n\n\n<\/p>\n\n\n\n
Here too, you do not have to display the make in the output, but I have chosen to do so to prove that the filter table actually works.<\/p>\n\n\n\n
ADDMISSINGITEMS<\/h3>\n\n\n\n While we may have looked at the core, everyday use of SUMMARIZECOLUMNS()<\/code> in the previous article, there are a couple of ways that this function can be extended that are worth knowing about. You may, one day need to use the ADDMISSINGITEMS()<\/code> function to force rows to be returned that SUMMARIZECOLUMNS()<\/code> would otherwise exclude.<\/p>\n\n\n\nThis option of SUMMARIZECOLUMNS()<\/code> forces the output of records (rows of data) that would not otherwise be returned by SUMMARIZECOLUMNS()<\/code> when output rows otherwise equate to empty or Null. This is probably best appreciated using a simple example. The following code wraps a SUMMARIZECOLUMNS()<\/code> function inside an ADDMISSINGITEMS()<\/code> function \u2013 and specifies that the date and make should nonetheless be returned if the row is otherwise empty:<\/p>\n\n\n\nEVALUATE\nADDMISSINGITEMS\n(\nDimDate[FullYear]\n,SUMMARIZECOLUMNS\n(\n DimDate[FullYear]\n,DimVehicle[Make]\n,FILTER(VALUES(DimVehicle[Make]), DimVehicle[Make] = \"Trabant\")\n,\"Total\", SUM(FactSales[SalePrice])\n)\n,DimDate[FullYear]\n)\nORDER BY DimDate[FullYear], DimVehicle[Make]<\/pre>\n\n\n\nRunning this code gives the following output:<\/p>\n\n\n\n<\/p>\n\n\n\n
To make the comparison clearer, running the DAX without ADDMISSINGITEMS<\/code> gives the following output:<\/p>\n\n\n\n<\/p>\n\n\n\n
Conclusion<\/h2>\n\n\n\n As you have learned, there is a small suite of DAX table functions that you can use to return data to paginated reports from a Power BI dataset. A good understanding of the available functions \u2013 and how they can be used, individually or together \u2013 will help you to deliver the output that you wish to display in your paginated reports.<\/p>\n\n\n\n
Remember that the aim of these two articles is not to provide an exhaustive and detailed overview of all the DAX table functions. The subject is simply too large for that. However, there are many excellent resources available if you wish to delve deeper into the myriad possibilities (and arcana) of table functions. What you have seen here is that you are not limited to SUMMARIZECOLUMNS() <\/code>when returning tabular output from a Power BI dataset to a paginated report, and that there are several other functions that you may find useful \u2013 or even necessary \u2013 in certain circumstances.<\/p>\n\n\n\nHowever, returning basic tables of data is only one step on the path to delivering paginated reports. You may well need to know a plethora of tricks and techniques to solve real-world challenges. The next article in this series introduces some of the tips that you may well need when creating paginated reports using a Power BI dataset.<\/p>\n\n\n\n\n FAQs: DAX table functions for paginated reports: Part 2<\/h2>\n\n 1. What are DAX variables and how do I use them?<\/h3>\n \n
DAX variables (declared with VAR) store a scalar value, measure result, or table expression within a RETURN block. Example: VAR SalesTotal = SUM(Sales[Amount]) VAR SalesTarget = 1000000 RETURN DIVIDE(SalesTotal, SalesTarget). Variables are evaluated once at the point of declaration – using a variable instead of repeating an expression avoids redundant recalculation. Table variables (VAR TableVar = FILTER(Table, condition)) store a table result that can be passed to FILTER, CALCULATETABLE, or other table functions.<\/p>\n <\/div>\n
2. What is the difference between GROUPBY and SUMMARIZE in DAX?<\/h3>\n \n
Both functions aggregate a table by specified grouping columns, but with different behaviour for measures. SUMMARIZE evaluates measures in the outer filter context – it can reference model measures. GROUPBY evaluates aggregation functions (SUMX, COUNTX, etc.) in the context of the grouped table – it is safer for complex calculations because it avoids the filter context manipulation issues that can occur with SUMMARIZE and measures. Microsoft recommends using SUMMARIZECOLUMNS (for most scenarios) or GROUPBY (for explicit table-level aggregation) over SUMMARIZE with measures.<\/p>\n <\/div>\n
3. What is CALCULATETABLE used for in DAX?<\/h3>\n \n
CALCULATETABLE returns a table that has been filtered by the specified filter conditions – it is the table equivalent of CALCULATE (which returns a scalar value). CALCULATETABLE(Table, filter1, filter2) returns all rows from Table that satisfy the filter conditions. It is particularly useful in paginated reports to create filtered datasets from a Power BI model, apply multiple filter criteria simultaneously, and avoid nested FILTER() calls. Filter tables (pre-built tables of valid values) can be passed as filter arguments instead of individual filter expressions.<\/p>\n <\/div>\n
4. How do EXCEPT, UNION, and INTERSECT work in DAX?<\/h3>\n \n
These three functions perform set operations on DAX tables with compatible column structures. EXCEPT(Table1, Table2) returns rows in Table1 that are not in Table2 (equivalent to SQL EXCEPT or NOT IN). UNION(Table1, Table2) combines all rows from both tables including duplicates (use DISTINCT or SUMMARIZE to deduplicate). INTERSECT(Table1, Table2) returns rows present in both tables. In paginated reports, these are commonly used to build exclusion filters (EXCEPT), combine multiple parameter value lists (UNION), or find common elements across two datasets (INTERSECT).<\/p>\n <\/div>\n <\/section>\n","protected":false},"excerpt":{"rendered":"
DAX for Power BI paginated reports Part 2: scalar and table DAX variables, EXCEPT\/UNION\/INTERSECT for set operations, GROUPBY and SUMMARIZE, and CALCULATETABLE with filter tables.…<\/p>\n","protected":false},"author":2181,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":true,"footnotes":""},"categories":[143528,53],"tags":[5134],"coauthors":[12351],"class_list":["post-93356","post","type-post","status-publish","format-standard","hentry","category-bi-sql-server","category-featured","tag-sql-prompt"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/93356","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/2181"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=93356"}],"version-history":[{"count":5,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/93356\/revisions"}],"predecessor-version":[{"id":109872,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/93356\/revisions\/109872"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=93356"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=93356"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=93356"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=93356"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}
![\"\"](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2022\/02\/word-image-1.png\")
<\/figure>\n\n\n\n![\"\"](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2022\/02\/word-image-2.png\")
<\/figure>\n\n\n\n![\"\"](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2022\/02\/word-image-3.png\")
<\/figure>\n\n\n\n![\"\"](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2022\/02\/word-image-4.png\")
<\/figure>\n\n\n\n![\"\"](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2022\/02\/word-image-5.png\")
<\/figure>\n\n\n\n![\"\"](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2022\/02\/word-image-6.png\")
<\/figure>\n\n\n\n![\"\"](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2022\/02\/word-image-7.png\")
<\/figure>\n\n\n\n![\"\"](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2022\/02\/word-image-8.png\")
<\/figure>\n\n\n\n![\"\"](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2022\/02\/word-image-9.png\")
<\/figure>\n\n\n\n![\"\"](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2022\/02\/word-image-10.png\")
<\/figure>\n\n\n\n![\"\"](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2022\/02\/word-image-11.png\")
<\/figure>\n\n\n\n