SUBSTITUTE()<\/code> function.<\/p>\n\n\n\nThe code snippet is the following:<\/p>\n\n\n\n
DEFINE\nVAR InputList = @InputListOfElements\nVAR NewInputList =\n SUBSTITUTE(\n InputList ,\n \",\",\n \"|\"\n )\nVAR CaseCount =\n PATHLENGTH (NewInputList)\nVAR NumberTable =\n GENERATESERIES(1, CaseCount, 1)\nVAR CustomerTable =\n GENERATE (\n NumberTable,\n VAR CurrentKey = [Value]\n RETURN\n ROW (\"Key\", PATHITEM (NewInputList, CurrentKey))\n )\nVAR GetKeyColumn =\n SELECTCOLUMNS (CustomerTable, \"Key\", [Key])\nVAR FilterTable =\n TREATAS (GetKeyColumn, DimCLient[CustomerID])\nEVALUATE\nCALCULATETABLE(\nSUMMARIZECOLUMNS(\n DimCLient[ClientName]\n,DimVehicle[Make]\n,DimVehicle[Model]\n,\"Sales\", SUM(FactSales[SalePrice])\n)\n,FilterTable\n)<\/pre>\n\n\n\nThe major challenge here is the (undocumented) limitation on the number of characters that a paginated report variable will accept. I estimate this to be around 11,000 characters approximately. So, should you need to parse longer lists, the solution is to use several input parameters in SSRS. The multiple input parameters are then combined into one variable in the DAX that returns the final output. This can be done in DAX using a simple concatenation of DAX variables rather like the example below:<\/p>\n\n\n\n
VAR INP01 = @Param01\nVAR INP02 = @Param02\nVAR INP03 = @Param03\n\nVAR InputList = INP01 & \",\" & INP02 & \",\" & INP03<\/pre>\n\n\n\nAlternatively, you can combine the variables using SSRS code by setting the input variable for the dataset as a function using code like the following:<\/p>\n\n\n\n
=Parameters!Param01.Value & IIF(IsNothing(Parameters!Param02.Value), \"\", \",\" & Parameters!Param02.Value) & IIF(IsNothing(Parameters!Param03.Value), \"\", \",\" & Parameters!Param03.Value)<\/pre>\n\n\n\nWhile going beyond the classic approaches to reporting, this simple evolution of paginated report parameter selection opens up a range of possibilities for report data selection.<\/p>\n\n\n\n
Pass RSCustomDaxFilter output to a table variable<\/h2>\n\n\n\n One frustrating limitation to the RSCustomDaxFilter<\/code> function is that it cannot be used twice with the same input variable inside the same DAX code block. Yet there will inevitably be times when you need to reuse the multiple filter elements to shape certain data outputs. One solution to this challenge is to use RSCustomDaxFilter<\/code> to populate a table variable. This table variable can then be reused as a filter table inside a CALCULATETABLE()<\/code> function. Indeed, you may prefer this technique as a coding approach for its elegance and versatility, even if you only need to use the elements in a multi-value parameter in DAX.<\/p>\n\n\n\nYou can see this approach applied in the following short piece of DAX:<\/p>\n\n\n\n
DEFINE\nVAR FilterTable = \nSUMMARIZECOLUMNS(\nRSCustomDaxFilter(@Make,EqualToCondition,[DimVehicle].[Make],String)\n)\nEVALUATE\nCALCULATETABLE(\nSUMMARIZECOLUMNS(\nDimCLient[ClientName]\n,DimGeography[CountryName]\n)\n,FilterTable\n)<\/pre>\n\n\n\nYou need to be aware that this code snippet will not run in DAX Studio as the RSCustomDaxFilter()<\/code> function is specific to SSRS. So you will have to test it inside a real SSRS report created using Power BI Report Builder.<\/p>\n\n\n\nOne paginated report UI requirement that surfaces fairly frequently is the capability to allow the user to select the actual parameter (and not the parameter contents) that can be used inside a query. In other words, you want to use a single query to produce different outputs depending on a user selecting one of several potential input parameters. I have seen this most frequently in cases where multiple date parameters are displayed in an SSRS report, and one date only must be specified to be used as the basis for a filter.<\/p>\n\n\n\n
In this example, however (given the simplicity of the sample dataset), assume that, instead of using dates, the colour and make parameters are passed to the DAX query along with a third parameter that indicates which of these two initial parameters should filter the output.<\/p>\n\n\n\n
To resume, the available parameters that are defined in the report are:<\/p>\n\n\n
\n
\nInputSelector<\/code> \u2013 The choice of which parameter will be applied (colour or make)<\/li>\n\n\n\nColour<\/code> \u2013 the list of colours to filter on<\/li>\n\n\n\nMake<\/code> \u2013 The list of makes to filter on<\/li>\n<\/ul>\n<\/div>\n\n\nThe actual DAX is shown immediately below, with the explanation afterwards.<\/p>\n\n\n\n
DEFINE\nVAR InputSelector = @InputSelector\nVAR ColourChoice = @Colour\nVAR MakeChoice = @Make\nVAR CoreTable = \nSUMMARIZECOLUMNS\n(\n DimGeography[SalesRegion]\n,DimGeography[CountryName]\n,DimVehicle[Make]\n,DimVehicle[Color]\n,\"Total Sales\", SUM(FactSales[SalePrice])\n,\"No. Sales\", COUNT(FactSales[SalePrice])\n)\nVAR SelectedColour =\nADDCOLUMNS(\nFILTER(\nCoreTable\n,DimVehicle[Color] = ColourChoice\n)\n,\"TableSelector\", \"Colour\"\n)\nVAR SelectedMake =\nADDCOLUMNS(\nFILTER(\nCoreTable\n,DimVehicle[Make] = MakeChoice\n)\n,\"TableSelector\", \"Make\"\n)\nVAR OutputData = \nSUMMARIZE\n(\nFILTER\n(\nUNION(SelectedColour, SelectedMake)\n,[TableSelector] = InputSelector \n)\n,DimGeography[SalesRegion]\n,DimGeography[CountryName]\n,DimVehicle[Color]\n,\"Total Sales\", SUM(FactSales[SalePrice])\n,\"No. Sales\", COUNT(FactSales[SalePrice])\n)\nEVALUATE\nOutputData<\/pre>\n\n\n\nRunning this code in DAX studio will display the Query Parameters dialog-where I have entered:<\/p>\n\n\n
\n
\nColour as the parameter to use in the query<\/li>\n\n\n\n Red as the colour (this will be used to filter the output)<\/li>\n\n\n\n Ferrari as the make (this will not be used in to filter the output)<\/li>\n<\/ul>\n<\/div>\n\n\n<\/p>\n\n\n\n
Using these parameter settings only returns red cars-whether they are Ferraris or not, as you can see below.<\/p>\n\n\n\n<\/p>\n\n\n\n
How it works:<\/p>\n\n\n
\n
\nThe SSRS variables are passed to DAX variables.<\/li>\n\n\n\n A table variable is created containing the required (unfiltered) output data. This table contains the two columns that could be used to filter data.<\/li>\n\n\n\n Two table variables (SelectedColour<\/code> and SelectedMake<\/code>) are created, each of which extends the initial table variable with ad added column containing the filter type (colour or make) that was used to filter the contents of the table variable.<\/li>\n\n\n\nThe two filtered table variables are UNIONed<\/code> into a fourth table variable. This table is filtered (using the added column) only to display the output using the chosen parameter type.<\/li>\n<\/ul>\n<\/div>\n\n\nNotes:<\/strong><\/p>\n\n\n\n
\nWhen the TableSelector<\/code> column (that is added to the table variables SelectedMake<\/code> and SelectedColour<\/code>) is used to filter the final OutputData<\/code> table variable, the column name must be in square brackets inside the FILTER()<\/code> function.<\/li>\n\n\n\nThe SUMMARIZE()<\/code> function is only required to remove the filter columns. If you want to keep the filter columns in the output, then you can remove the SUMMARIZE().<\/code><\/li>\n\n\n\nAlternatively, it is possible to create multiple datasets instead (one for each filter type) and have multiple tables for the output in the report \u2013 and then use the selector variable to control the visibility of objects in the actual report. However, this approach entails data being sent to the report from two or more queries \u2013 and this can be voluminous. Moreover, this alternative approach rapidly makes for a complex report if there are multiple visuals that use the alternative output data.<\/li>\n\n\n\n It is, of course, possible to have more than two alternative filters. All you have to do is to create multiple table variables (one for each of the alternative selections) then nest UNION()<\/code> functions to aggregate all data into the OutputData<\/code> table.<\/li>\n<\/ul>\n<\/div>\n\n\nWhile it is clearly impossible to cover every DAX challenge that you will face when developing paginated reports based on a Power BI dataset, there are some limitations that you need to be aware of \u2013 particularly before promising your clients or bosses a failsafe reporting solution.<\/p>\n\n\n\n
Large dataset output<\/h2>\n\n\n\n It is fair to say, at risk of extreme generalisation, that Power BI and DAX are designed to aggregate and resume data rather than deliver large amounts of list-based data to users. Consequently, you need to be aware that very wide and deep paginated reports could simply overload the Power BI service and cause timeouts.<\/p>\n\n\n\n
Clearly, the definitions of what defines a \u201cwide and deep\u201d report are open to discussion. Equally, much will depend on the resource level that you have chosen and the stress placed on the Power BI Premium service by multiple simultaneous user requests (for dashboards and paginated reports), as well as any system configuration tweaks that you have carried out.<\/p>\n\n\n\n
Nonetheless, there are limits on the amount of data that can be returned using paginated reports in Power BI, and it is important not to attempt to use an SSRS-like approach to use the Power BI service as a data export and delivery vehicle.<\/p>\n\n\n\n
The paginated report interface does have its limits as far as the number of elements that can be selected from a multi-value parameter list. While no one seems to know the exact value, it\u2019s obvious when it has been reached because a report stops functioning correctly. This is something else that has to be lived within paginated reports. The only real solutions are to apply interface tweaks such as having a hierarchy of parameters where selecting one parameter restricts the selection in a second parameter \u2013 and so on. This equates to the Power BI dashboard technique of using hierarchical slicers.<\/p>\n\n\n\n
Conclusion<\/h2>\n\n\n\n In this article \u2013 along with the previous articles \u2013 you have learned the core approaches needed to use DAX effectively to query Power BI datasets for paginated reports. You can now deliver these reports either as stand-alone elements accessed from Power BI workspaces (or, better still, from the Power BI App) or embedded in Power BI dashboards using the paginated report visual.<\/p>\n\n\n\n
You now have, in a single platform, a wide-ranging series of options to deliver the data that your users require both as dashboards and as list-style reports. Moreover, all the varied output styles can be created from a single source of data \u2013 a Power BI data model.<\/p>\n\n\n\n\n FAQs: Tips and tricks with DAX table functions in paginated reports<\/h2>\n\n 1. How do I use NOT IN in DAX for paginated reports?<\/h3>\n \n
DAX has the IN operator but no direct NOT IN equivalent. Simulate it using EXCEPT: EXCEPT(ALL(TableName[Column]), FilterTable) returns all rows in the column that are not in the filter table. Alternatively, use FILTER with a NOT condition: FILTER(ALL(TableName[Column]), NOT TableName[Column] IN {value1, value2}). For paginated reports with parameter inputs, build the exclusion filter from the parameter values and apply EXCEPT against the full column table. This approach is covered in the ‘Imitating NOT IN when filtering’ section of this article.<\/p>\n <\/div>\n
2. How do I build a complex OR filter in DAX for a paginated report?<\/h3>\n \n
DAX’s FILTER function and CALCULATETABLE stack conditions with AND logic by default. To apply OR logic across different columns, use UNION to combine separate FILTER results: UNION(FILTER(Table, Table[Column1] = @Param1), FILTER(Table, Table[Column2] = @Param2)) returns rows matching either condition. For multi-select parameters where OR must apply across multiple values, this approach is extended using UNION across each parameter value. The CALCULATETABLE function can also be used with an OR condition in its filter argument for simpler OR cases.<\/p>\n <\/div>\n
3. How do I handle a comma-delimited list of values as a DAX filter input?<\/h3>\n \n
Paginated reports can accept text input that contains a comma-delimited list (e.g., ‘Ferrari,Lamborghini,Bugatti’). To use this as a DAX filter, the RSCustomDaxFilter function (or equivalent PATHCONTAINS approach) processes the string input into a filter table. The approach: split the input string into individual values using a helper function, create a table of values, then use FILTER or CALCULATETABLE to match against the column. The ‘Using a comma-delimited list of input values to filter the output’ section of this article provides a complete implementation.<\/p>\n <\/div>\n
4. How do I add a 'No Selection' option to a multi-value parameter in a Power BI paginated report?<\/h3>\n \n
Use a DAX expression that adds an additional row to the parameter dataset: UNION(DATATABLE(‘ParameterField’, STRING, {{‘<No Selection>’}}), SUMMARIZE(Table, Table[Column])). This adds ‘<No Selection>’ as the first item in the parameter dropdown. In the DAX query for the report dataset, check for this value and return all rows if it is selected: IF(SELECTEDVALUE(ParameterTable[ParameterField]) = ‘<No Selection>’, Table, FILTER(Table, Table[Column] = SELECTEDVALUE(ParameterTable[ParameterField]))). The complete pattern is in the ‘Adding <No Selection> to the parameter list’ section.<\/p>\n <\/div>\n <\/section>\n","protected":false},"excerpt":{"rendered":"
Advanced DAX patterns for Power BI paginated reports: simulate NOT IN, remove filter columns, build complex OR filters across multi-select parameters, handle comma-delimited list inputs, and manage large dataset output.…<\/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-93601","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\/93601","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=93601"}],"version-history":[{"count":9,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/93601\/revisions"}],"predecessor-version":[{"id":109855,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/93601\/revisions\/109855"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=93601"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=93601"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=93601"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=93601"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}
![\"\"](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2022\/03\/word-image-20.png\")
<\/figure>\n\n\n\n![\"\"](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2022\/03\/word-image-21.png\")
<\/figure>\n\n\n\n![\"\"](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2022\/03\/word-image-22.png\")
<\/figure>\n\n\n\n![\"\"](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2022\/03\/word-image-23.png\")
<\/figure>\n\n\n\n![\"\"](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2022\/03\/word-image-24.png\")
<\/figure>\n\n\n\n![\"\"](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2022\/03\/word-image-25.png\")
<\/figure>\n\n\n\n![\"\"](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2022\/03\/word-image-26.png\")
<\/figure>\n\n\n\n![\"\"](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2022\/03\/word-image-27.png\")
<\/figure>\n\n\n\n![\"\"](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2022\/03\/word-image-28.png\")
<\/figure>\n\n\n\n![\"\"](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2022\/03\/word-image-29.png\")
<\/figure>\n\n\n\n