{"id":93031,"date":"2021-12-17T15:50:13","date_gmt":"2021-12-17T15:50:13","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=93031"},"modified":"2026-03-06T14:14:58","modified_gmt":"2026-03-06T14:14:58","slug":"how-to-filter-dax-for-paginated-reports","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/bi-sql-server\/how-to-filter-dax-for-paginated-reports\/","title":{"rendered":"DAX Filtering for Paginated Reports: A Guide"},"content":{"rendered":"\n

DAX filtering in paginated reports works differently from SQL – you can’t simply add a WHERE clause. Instead, use the SUMMARIZECOLUMNS function with filter arguments to control what data appears in your paginated report output. This article covers the complete filtering workflow: basic column filtering, OR logic (which requires special handling in DAX), calculated filter expressions, and the rscustomdaxfilter parameter technique for passing user selections from Report Builder into your DAX queries. All examples use the Power BI Premium service with paginated reports.<\/p>\n\n\n

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

    \n
  1. Introduction to DAX for paginated reports<\/a><\/li>\n
  2. How to filter DAX for paginated reports<\/a><\/li>\n
  3. \u00a0DAX table functions for paginated reports: Part 1<\/a><\/li>\n
  4. DAX table functions for paginated reports: Part 2<\/a><\/li>\n
  5. Tips and tricks with DAX table functions in paginated reports<\/a><\/li>\n<\/ol>\n\n\n\n\n

    In the previous article<\/a> of this short series, you learned the fundamentals of creating datasets using DAX to populate paginated reports delivered using the Power BI Premium service. The next step is to appreciate the practicalities \u2013 and subtleties \u2013 of how data can be filtered using DAX for paginated report output.<\/p>\n\n\n\n

    As most, if not all, report developers come from an SQL background, it may seem overkill to devote an entire article to filtering data. However, DAX is very unlike SQL as far as filtering output data is concerned. Something as simple as classic OR logic needs to be handled differently from the techniques you may be used to \u2013 either as a SQL or as a Power BI developer. To ensure that you can deliver the report data that you need to populate paginated reports, take a detailed look at how to filter data in DAX datasets using the core SUMMARIZECOLUMNS()<\/code> function.<\/p>\n\n\n\n

    Like all the articles in this series, this article uses a sample dataset named CarsDataWarehouse.Pbix<\/em><\/a> that you can download and use as the basis for practicing and testing DAX data output techniques. If you wish to understand the dataset structure, please refer to the first article in the series.<\/p>\n\n\n\n

    Basic Data Output to SSRS Using SUMMARIZECOLUMNS()<\/h2>\n\n\n\n

    One positive thing that I can say about the Power BI Report Builder Query Designer is that it introduces you to the core DAX function that you are likely to use when developing DAX queries for paginated reports: SUMMARIZECOLUMNS()<\/code>.<\/p>\n\n\n\n

    SUMMARIZECOLUMNS()<\/code> is probably the DAX function that most people generally use to produce tables of data – which makes it an ideal function to query Power BI dataset data for SSRS reports. By a happy coincidence, Report Builder also uses the function to generate DAX. Consequently, it is the default option for report developers who need to use DAX data sources.<\/p>\n\n\n\n

    SUMMARIZECOLUMNS()<\/code> is so powerful because it allows you to assemble, filter, and aggregate data from a Power BI dataset into a single tabular output for paginated reports. Indeed, it requires that you apply these elements in a specific order inside the SUMMARIZECOLUMNS()<\/code> function:<\/p>\n\n\n

    \n
      \n
    1. A list of fields from the data model<\/li>\n\n\n\n
    2. Any required Filters<\/li>\n\n\n\n
    3. Any required Aggregations<\/li>\n<\/ol>\n<\/div>\n\n\n

      It is worth noting that parts (2) & (3) in this list are optional. You do not have to filter data or add aggregations and can simply use SUMMARIZECOLUMNS()<\/code> to output the list of data you need. This means that any of the following approaches will work.<\/p>\n\n\n\n

      Read also:<\/strong>
      SSRS basics and overview<\/a>
      Insights from the SSRS database<\/a><\/p>\n\n\n\n

      Column List Only<\/h3>\n\n\n\n

      Suppose all you want to do is return a list of columns to a paginated report. In that case, you can manually write code like the following DAX snippet developed in DAX Studio that you then test and subsequently copy and paste into Power BI Report Builder. Of course, when I say \u201cmanually\u201d write code, I mean search for the fields, drag and drop them into the query pane, and use autocomplete to enter any DAX keywords.<\/p>\n\n\n\n

      EVALUATE\nSUMMARIZECOLUMNS\n(\n DimVehicle[Make]\n,DimVehicle[Model]\n)<\/pre>\n\n\n\n
      The output from this short piece of DAX is (in a shortened form):<\/p>\n\n\n\n
      \"\"<\/figure>\n\n\n\n
      <\/p>\n\n\n\n
      While looking at core DAX for report output, it is perhaps worth a quick detour to explain the EVALUATE<\/code> function. This function is the DAX statement needed to execute a query and return data. In simple DAX queries, all that you need to do is to start the query with the keyword EVALUATE<\/code>. As queries become more intricate (and as you will discover in the course of these four articles), this initial approach can be extended to handle more complex requirements.<\/p>\n\n\n\n
      There are a couple of points that it is worth noting at this juncture:<\/p>\n\n\n
      \n