{"id":92822,"date":"2021-11-15T23:14:21","date_gmt":"2021-11-15T23:14:21","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=92822"},"modified":"2021-12-17T15:54:51","modified_gmt":"2021-12-17T15:54:51","slug":"introduction-to-dax-for-paginated-reports","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/bi-sql-server\/introduction-to-dax-for-paginated-reports\/","title":{"rendered":"Introduction to DAX for paginated reports"},"content":{"rendered":"<p><strong>The series so far:<\/strong><\/p>\n<ol>\n<li><a href=\"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/bi-sql-server\/introduction-to-dax-for-paginated-reports\/\">Introduction to DAX for paginated reports<\/a><\/li>\n<li><a href=\"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/bi-sql-server\/how-to-filter-dax-for-paginated-reports\/\">How to filter DAX for paginated reports<\/a><\/li>\n<li><a href=\"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/bi-sql-server\/dax-table-functions-for-paginated-reports-part-1\/\"> \u00a0DAX table functions for paginated reports: Part 1<\/a><\/li>\n<li><a href=\"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/bi-sql-server\/dax-table-functions-for-paginated-reports-part-2\/\">DAX table functions for paginated reports: Part 2<\/a><\/li>\n<li><a href=\"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/bi-sql-server\/tips-and-tricks-with-dax-table-functions-in-paginated-reports\/\">Tips and tricks with DAX table functions in paginated reports<\/a><\/li>\n<\/ol>\n\n<p>SQL Server Reporting Services (SSRS) may have been around for nearly 20 years, but it simply refuses to go away. Its latest lease of life is evidenced in the release of the Paginated Report visual for Power BI that allows developers to include paginated reports inside Power BI dashboards. In many ways, this is a natural extension of the existing ability of the Power BI Premium Service to load and display paginated reports \u2013 and of course, the well-established use of SSRS as the reporting tool of choice to visualise information held in tabular SQL Server Analysis Services data warehouses.<\/p>\n<p>However, if you are a traditional SSRS report developer, who is in the habit of using SQL as the query language for your reports, then using DAX to query source data can be initially disconcerting. Equally, the DAX that you use to extract data for reports can be very different from the DAX that you may be used to writing when creating Power BI dashboards (or even extending Excel Pivot tables).<\/p>\n<p>The aim of this article \u2013 and the three that follow \u2013 is to help you overcome any initial challenges when sourcing data for paginated reports from a Power BI dataset. This includes a series of suggestions to make your life easier when delivering paginated reports &#8211; whether as a separate report or as a visual inside a Power BI dashboard with Power BI Premium (which includes Premium per user).<\/p>\n<p>What I am not attempting to do is to provide either an introduction to paginated report development or to DAX. I am presuming that you have a grasp of the fundamentals of each of these and that your objective is to adapt your existing knowledge to use DAX as the source of data for paginated reports. This means that I will not explain how to create Power BI dashboards, load datasets into the Power BI Service, create dataflows, or write DAX measures \u2013 or indeed any of the \u201cclassic\u201d Power BI techniques.<\/p>\n<p>Within the scope of these objectives, it is time to move on to an initial overview of how to create paginated reports using DAX.<\/p>\n<h2>The paginated report developer\u2019s toolkit<\/h2>\n<p>Creating and perfecting paginated reports for Power BI requires a very simple set of tools. The good news is that all of them are completely free. The bad news is that paginated reports are only available in a Premium or Premium per user workspace.<\/p>\n<p>The core tool used to create the paginated reports that you will subsequently upload to the Power BI Service is <a href=\"https:\/\/www.microsoft.com\/en-us\/download\/details.aspx?id=58158\"><strong>Power BI Report Builder<\/strong><\/a><strong>.<\/strong> I suggest this as the report development tool simply because:<\/p>\n<ul>\n<li>It is free, quick to install, and has no licensing hassles<\/li>\n<li>It connects automatically to Power BI datasets in the Power BI Service<\/li>\n<\/ul>\n<p>To write and debug DAX, I strongly advise that you download and install the brilliant and irreplaceable <a href=\"https:\/\/daxstudio.org\/\"><strong>DAX Studio<\/strong><\/a><strong>.<\/strong><\/p>\n<p>Finally, an advanced text editor such as <strong>Notepad++<\/strong> is invaluable for some of the more advanced tweaking of the <em>.Rdl<\/em> files that you create using Power BI Report Builder.<\/p>\n<h2>Sample data<\/h2>\n<p>To allow you to concentrate on DAX, and not waste time setting up a complex environment, the accompanying sample data for these articles is a single Power BI Desktop file. This file (<a href=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2021\/11\/CarsDataWarehouse.pbix\">Car<\/a><a href=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2021\/11\/CarsDataWarehouse.pbix\">s<\/a><a href=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2021\/11\/CarsDataWarehouse.pbix\">Data<\/a><a href=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2021\/11\/CarsDataWarehouse.pbix\">Warehouse.pbix<\/a>) contains one fact table and four dimensions. You can see the star schema of the data in the following image:<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"907\" height=\"693\" class=\"wp-image-92823\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2021\/11\/word-image-7.png\" \/><\/p>\n<p>This dataset is deliberately lightweight and contains only a few hundred records to ensure that you spend as little time as possible waiting on query execution when you run your own DAX queries.<\/p>\n<h2>Power BI Dashboard and report integration<\/h2>\n<p>If you wish to follow the examples in these articles, you will need to imitate a real-world Power BI environment. I will presume that you have access to a Power BI Premium environment and have the rights to upload both <em>.Pbix<\/em> and <em>.Rdl<\/em> files. The unfortunate limitation at present is that you cannot perfect your paginated report integration skills if all you have is a free Power BI account. To get started, upload the <a href=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2021\/11\/CarsDataWarehouse.pbix\">CarsDataWarehouse.pbix<\/a> file to Power BI Service.<\/p>\n<h2>Building paginated reports using Power BI datasets<\/h2>\n<p>To ensure that the prerequisites are in place, here is how you can connect to an existing Power BI dataset in the Power BI Service from Power BI Report Builder. This dataset can be as simple as a version of the sample dashboard <em>CarsDataWarehouse.Pbix<\/em> that you have previously uploaded.<\/p>\n<ol>\n<li>Open <em>Power BI Report Builder<\/em>.<\/li>\n<li>Create a <em>Blank Report<\/em><\/li>\n<li>Right-click <em>Data Sources<\/em> and select <em>Add Power BI Dataset<\/em> connection.<\/li>\n<li>Select the dataset in the dialog as shown below and click <em>Select<\/em>.<\/li>\n<\/ol>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"910\" height=\"634\" class=\"wp-image-92824\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2021\/11\/word-image-8.png\" \/><\/p>\n<p>You can now build SSRS reports that will use the data in the Power BI Service using DAX as the query language.<\/p>\n<h2>Automated query writing<\/h2>\n<p>If the data you need is extremely simple (a list without parameters and no filtering), then you can have Power BI Report Builder write the DAX for you, like this:<\/p>\n<ol>\n<li>Right-click on <em>Datasets<\/em> and click <em>Add<\/em> <em>Dataset<\/em>.<\/li>\n<li>Select the <em>CarsDataWarehouse<\/em> data source.<\/li>\n<li>In the <em>Dataset<\/em> <em>Properties<\/em> dialog, click on the <em>Query<\/em> <em>Designer<\/em> button.<\/li>\n<li>Drag the <em>Attributes<\/em> and measures you wish to include in the report dataset into the lower right-hand pane.<\/li>\n<li>Test the query by clicking <em>Click to execute the query<\/em>.<\/li>\n<li>Click <em>OK<\/em>. You will see the resulting, automatically-generated DAX query in the <em>Query<\/em> area as shown below:<\/li>\n<\/ol>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"680\" height=\"636\" class=\"wp-image-92825\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2021\/11\/word-image-9.png\" \/><\/p>\n<ol start=\"7\">\n<li>Click <em>OK<\/em> to create the dataset.<\/li>\n<\/ol>\n<p>As you can see, this is very simple and very easy. However, I would never suggest that you build any but the simplest of output datasets in this way. The reasons for avoiding this approach are:<\/p>\n<ul>\n<li>It only works for simple lists.<\/li>\n<li>It creates excessively complex DAX that is hard to understand and modify when you add parameters and filters.<\/li>\n<li>It has an annoying tendency to crash for complex queries<\/li>\n<li>Using the <em>Query Designer<\/em> automatically overwrites any existing query \u2013 even carefully hand-crafted DAX queries \u2013 which rapidly becomes extremely frustrating.<\/li>\n<\/ul>\n<p>Consequently, I advise avoiding the query designer as far as possible and using DAX Studio to write \u2013 and perfect \u2013 the DAX that you use to drive your paginated reports.<\/p>\n<h2>Uploading paginated reports to the Power BI Service<\/h2>\n<p>One of the advantages of using Power BI Report Builder to create <em>.RDL<\/em> files, is that it imitates Power BI Desktop in that it can connect to the Power BI Service and update the current file in a couple of clicks. Simply click the Publish button in the Home menu and choose the destination workspace for the current paginated report file. Of course, you will have to log in to the Power BI Premium service (just as you would when developing dashboards using Power BI Desktop) first.<\/p>\n<p>Alternatively, you can log in to Power BI and upload an <em>.Rdl<\/em> file to the Power BI Service just as you would upload a <em>.pbix<\/em> file.<\/p>\n<h2>Alternatives to the Query Designer<\/h2>\n<p>If the <em>Query Designer<\/em> inside <em>Power BI Report Builder<\/em> is best avoided, what is the alternative? Quite simply, the most efficient alternative that I can suggest is:<\/p>\n<ol>\n<li>Build and test DAX queries using <em>DAX Studio<\/em>.<\/li>\n<li>Copy the query into <em>Power BI Report Builder<\/em> like this:\n<ol style=\"list-style-type: lower-alpha;\">\n<li>Right-click on <em>Datasets<\/em> and click <em>Add Dataset<\/em>.<\/li>\n<li>Click on the <em>Expression<\/em> <em>Editor<\/em> (<em>Fx<\/em>) button to the right of the <em>Query<\/em> area.<\/li>\n<li>Paste the DAX query (from DAX Studio) into the <em>Expression<\/em> <em>Editor<\/em>, as shown in the following figure:<\/li>\n<\/ol>\n<\/li>\n<\/ol>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"545\" height=\"616\" class=\"wp-image-92826\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2021\/11\/word-image-10.png\" \/><\/p>\n<ol start=\"3\">\n<li>Click <em>OK<\/em> twice.<\/li>\n<\/ol>\n<p>This approach has multiple advantages. Amongst the main ones are:<\/p>\n<ul>\n<li>You can build, test and debug DAX in an IDE designed specifically for DAX development \u2013 including searching for attributes in the dataset and drag and drop, search and replace, etc.<\/li>\n<li>DAX Studio provides comprehensible error messages if anything does not work quite as expected.<\/li>\n<li>Coding errors are less likely which is particularly important as many DAX errors cause the Power BI Report Builder either to hang or to crash.<\/li>\n<li>You can preview the output data.<\/li>\n<li>You can develop complex DAX that is simply not practical to write in the notepad-style editor of Power BI Report Builder.<\/li>\n<\/ul>\n<h2>SSRS parameters in DAX<\/h2>\n<p>Of course, you are inevitably going to need to filter the data when it comes to returning data from a Power BI dataset to a paginated report. SSRS has handled user parameters from its outset as a data filtering technique and can, of course, handle parameter passing to DAX-based Power BI data sources.<\/p>\n<h3>Creating DAX-driven parameters<\/h3>\n<p>The first thing to do is to populate SSRS parameters from the underlying dataset using DAX. The basic principles concerning populating DAX parameters are very similar to those that you may be used to applying when using SSRS with SQL or MDX as the query languages:<\/p>\n<ul>\n<li>Create one <em>DataSet<\/em> in the paginated report for each SSRS parameter that you need to populate.<\/li>\n<li>Create another separate dataset if you are pre-selecting multiple default items from a parameter list automatically.<\/li>\n<\/ul>\n<h3>Populating the parameter list<\/h3>\n<p>The DAX that you use to query a Power BI dataset to return a list of parameter elements is straightforward. It is nearly always something like the following:<\/p>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk\">EVALUATE\r\nSUMMARIZECOLUMNS\r\n(\r\nDimGeography[CountryName]\r\n)<\/pre>\n<p>It is worth noting that:<\/p>\n<ul>\n<li>You do not need to specify a distinct list as <code>SUMMARIZECOLUMNS()<\/code> does this for you automatically.<\/li>\n<li>Remember that the parameter values are case-sensitive if you enter one or more values manually in the list of default values.\u00a0You then use this dataset as the source for the available values in the parameter list by selecting <em>Get values from a query<\/em> as the <em>Available Values<\/em> setting in the parameter properties.<\/li>\n<\/ul>\n<h3>Date-based parameters<\/h3>\n<p>If you are setting a start date (for instance) as the default value for a parameter, the minimum date value from a field in a dataset can be calculated using a DAX query that applied the <code>FIRSTDATE()<\/code> function like this:<\/p>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk\">EVALUATE FIRSTDATE(VALUES(DimDate[DateKey]))<\/pre>\n<p>Conversely, that last date in a field can be queried using the <code>LASTDATE()<\/code> function \u2013 something like this<\/p>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk\">EVALUATE LASTDATE(VALUES(DimDate[DateKey]))<\/pre>\n<h2>Applying parameters in paginated reports<\/h2>\n<p>Once you have created one or more parameters in a paginated report, you will, of course, need to apply them to the DAX that you are creating to return data to the report. As you just saw, external parameters in DAX are simply variables that start with the <code>@<\/code> sign. As you might expect, the art is to apply these variables inside the filters so that the resulting data conforms to your requirements.<\/p>\n<h3>Simple parameter passing<\/h3>\n<p>To start at the beginning, let\u2019s take a (very) simple output using a single filter that does not yet use a parameter \u2013 like this:<\/p>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk\">EVALUATE\r\nSUMMARIZECOLUMNS(\r\n\u00a0DimCLient[ClientName]\r\n,DimVehicle[Make]\r\n,DimVehicle[Model]\r\n,FILTER(VALUES(DimVehicle[Color]), DimVehicle[Color] = \"Red\")\r\n,\"Sales\", SUM(FactSales[SalePrice])\r\n)<\/pre>\n<p>The shortened output from this short piece of DAX is:<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"306\" height=\"180\" class=\"wp-image-92827\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2021\/11\/word-image-11.png\" \/><\/p>\n<p>Assuming that you have created an SSRS parameter that contains a list of colours, and that the name of this parameter is <code>@Colour<\/code>, the previous DAX snippet, tweaked to enable parameter passing into the DAX, becomes:<\/p>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk\">EVALUATE\r\nSUMMARIZECOLUMNS(\r\n\u00a0DimCLient[ClientName]\r\n,DimVehicle[Make]\r\n,DimVehicle[Model]\r\n,FILTER(VALUES(DimVehicle[Color]), DimVehicle[Color] = @Colour)\r\n,\"Sales\", SUM(FactSales[SalePrice])\r\n)<\/pre>\n<p>This also introduces the use of external parameters in DAX. As you can see, they are extremely simple. An external parameter:<\/p>\n<ul>\n<li>Starts with <code>@<\/code><\/li>\n<li>Does not contain spaces<\/li>\n<\/ul>\n<p>When creating a dataset filtered by a parameter, you must create the query first without the filter. Link the parameter in the Parameters tab and then add the filter. You&#8217;ll get an error message if you try to add the filter before the parameter is linked.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"670\" height=\"224\" class=\"wp-image-92828\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2021\/11\/graphical-user-interface-text-application-email.png\" alt=\"Graphical user interface, text, application, email\n\nDescription automatically generated\" \/><\/p>\n<p><strong>Notes:<\/strong><\/p>\n<p>As previously mentioned, DAX Studio allows you to test this approach by entering parameters when you run the DAX. Running the above DAX will display the following dialog:<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"400\" height=\"350\" class=\"wp-image-92829\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2021\/11\/word-image-12.png\" \/><\/p>\n<p>A single parameter can be reused at multiple places inside the DAX.<\/p>\n<ul>\n<li>Date parameters require that you enclose the date parameter in the <code>VALUES()<\/code> function \u2013 like this (for a parameter named <code>@EndDate<\/code>):<\/li>\n<\/ul>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk\">,FILTER(VALUES(DimDate[DateKey]), DimDate[DateKey] &gt;= VALUE(\"12\/29\/2020 12:00:00 AM\") &amp;&amp; DimDate[DateKey]&lt;= <strong>VALUE(@EndDate)<\/strong>)<\/pre>\n<p>It is worth noting that, given the fragility of Power BI Report Builder, you have to add, update or remove any SSRS parameters that you are using in a report dataset <strong>before<\/strong> modifying the query \u2013 or, alternatively, modify the query in Notepad++ then add the parameters in Power BI Report Builder. In any case, the input parameters must be aligned to the parameters used in the DAX or Power BI Report Builder will probably hang until you close it and start over. It is also worth remembering that any parameters that you define <strong>must<\/strong> be used in the DAX. In contrast, merely adding a parameter to a report and not using it in the dataset will not produce any negative effects.<\/p>\n<h3>Populate parameters with all elements selected<\/h3>\n<p>The DAX needed if you want to define an SSRS dataset that sets all the elements in a multi-select parameter list as default values are slightly out of the ordinary. You need to add the <code>KEEPFILTERS()<\/code> function to the DAX, like this:<\/p>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk\">EVALUATE\r\nSUMMARIZECOLUMNS\r\n(\r\nDimGeography[CountryName]\r\n,KEEPFILTERS(FILTER(ALL(DimGeography[CountryName])\r\n             ,NOT ISBLANK((DimGeography[CountryName]))))\r\n)<\/pre>\n<p>You then use this dataset as the source for the default values in the parameter list by selecting <em>Get values from a query<\/em> as the <em>Available Values<\/em> setting in the parameter properties.<\/p>\n<p>Please note that the effect of this definition of default values does not currently display correctly in preview in Power BI Report Builder. To see it work as you expect it to, you have to load the report into the Power BI Service and run the report from there.<\/p>\n<p>Be warned, however, that a multi-value parameter needs careful handling inside the DAX itself. You need to look ahead to the next but one section <em>RSCustomDaxFilter<\/em> actually to use the multiple elements that you have selected.<\/p>\n<h3>Parameterized parameters<\/h3>\n<p>Very much as you do for SQL sources, you can create <em>cascading<\/em> parameters where one parameter\u2019s output becomes an input parameter for a second parameter. This allows you to imitate the hierarchical slicers in Power BI dashboards.<\/p>\n<p>For example, assuming that a parameter named <code>Make<\/code> exists, and that you want the selected make to filter the items displayed in a <code>Model<\/code> parameter, you can use the following DAX to return the list of available elements in the <code>Model<\/code> parameter:<\/p>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk\">EVALUATE\r\nSUMMARIZECOLUMNS(\r\nDimVehicle[Model]\r\n,FILTER(VALUES(DimVehicle[Make]), DimVehicle[Make] = @Make)\r\n)<\/pre>\n<p>Of course, this requires a couple of basic structural elements:<\/p>\n<ul>\n<li>The <code>Make<\/code> parameter is moved above the <code>Model<\/code> parameter in the Parameters list in Power BI Report Developer.<\/li>\n<li>You define <code>Make<\/code> as an input parameter for the <code>Model<\/code> dataset you create using the code snippet above.<\/li>\n<li>The <code>Model<\/code> parameter can then be used to filter the main report<\/li>\n<\/ul>\n<h3>RSCustomDaxFilter<\/h3>\n<p>Handling multi-select parameters is one aspect of parameter passing from SSRS to DAX that was tricky initially (but that has fortunately been solved for a while now).<\/p>\n<p>The currently recommended approach is to use the SSRS function <code>RSCustomDaxFilter()<\/code> to handle multiple selections in the SSRS report interface. This function is not standard DAX (it is not recognised by DAX Studio, for instance) and only works in the DAX used in .Rdl files.<\/p>\n<p>This function required four elements. They are:<\/p>\n<ol>\n<li>Parameter Name<\/li>\n<li>Comparison\/Condition<\/li>\n<li>Field to use<\/li>\n<li>Data Type<\/li>\n<\/ol>\n<p>The <code>RSCustomDaxFilter()<\/code> function is probably best explained using a simple example. Take a look at the following piece of DAX:<\/p>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk\">EVALUATE\r\nSUMMARIZECOLUMNS(\r\n\u00a0DimCLient[ClientName]\r\n,DimVehicle[Make]\r\n,DimVehicle[Model]\r\n,RSCustomDaxFilter(@Colour, EqualToCondition, [DimVehicle].[Color], String)\r\n,\"Sales\", SUM(FactSales[SalePrice])\r\n)<\/pre>\n<p>This filter takes multiple selected elements passed from the <em>Colour<\/em> parameter popup list in a paginated report and applies them as an OR filter to the underlying DAX.<\/p>\n<p>Let\u2019s take a closer look at the four elements of the function:<\/p>\n<ol>\n<li>ParameterName \u2013 <code>(@Colour<\/code> in this example). This is simple as it is the name of the input parameter as defined in the Parameters pane of the Dataset properties.<\/li>\n<li>Comparison\/Condition \u2013 (<code>EqualToCondition<\/code> in this example). This is the equality operator (=).<\/li>\n<li>Field to use\u2013 (<code>[DimVehicle].[Color]<\/code>in this example). This refers to the dataset field that must be used to filter data.<\/li>\n<li>Data Type\u2013 (String in this example) This specifies the datatype of the parameter.<\/li>\n<\/ol>\n<p><strong>Notes:<\/strong><\/p>\n<p>You cannot write the field name inside the <code>RSCustomDaxFilter()<\/code> function as <code>CaseProduct[Key Ingredients]<\/code> or <code>\u2018CaseProduct\u2019[Key Ingredients]<\/code> (as you would in normal DAX). You must wrap both the table and the field names in square brackets and separate them with a period. I suspect that this is a possible hangover from the MDX-style approach used in SSRS when handling non-SQL data sources.<\/p>\n<p>The possible datatypes that you can use are:<\/p>\n<ul>\n<li>Int64<\/li>\n<li>String<\/li>\n<li>Double<\/li>\n<li>DateTime<\/li>\n<li>Boolean<\/li>\n<li>Currency<\/li>\n<\/ul>\n<p>The possible conditions that you can use are:<\/p>\n<ul>\n<li>EqualToCondition<\/li>\n<li>Not EqualToCondition<\/li>\n<\/ul>\n<p>To make things clearer, the following is a valid application of <code>RSCustomDaxFilter()<\/code>:<\/p>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk\">RSCustomDaxFilter(@SalesAmount,NotEqualToCondition,[FactSales].[SalePrice],Double)<\/pre>\n<p>In practice, you can use <code>RSCustomDaxFilter()<\/code> where you would otherwise use <code>FILTER()<\/code> inside the <code>SUMMARIZECOLUMNS()<\/code> function when you need to accept multiple alternative parameter inputs to filter data. You can, of course, mix the two approaches as well as using use the complete range of possibilities of <code>SUMMARIZECOLUMNS()<\/code> as the following example shows:<\/p>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk\">EVALUATE\r\nSUMMARIZECOLUMNS(\r\n\u00a0DimCLient[ClientName]\r\n,DimVehicle[Make]\r\n,DimVehicle[Model]\r\n,FILTER(\r\n        VALUES(DimDate[DateKey])\r\n        ,AND(DimDate[DateKey] &gt;= VALUE(\"01\/01\/2020\")\r\n        ,DimDate[DateKey]&lt;= VALUE(\"12\/30\/2022\"))\r\n        )\r\n,FILTER(VALUES(DimVehicle[Color]), DimVehicle[Color] = @Colour)\r\n,RSCustomDaxFilter(@SalesAmount\r\n                   ,EqualToCondition\r\n                   ,[FactSales].[SalePrice]\r\n                   ,Double)\r\n,\"Sales\", SUM(FactSales[SalePrice])\r\n)<\/pre>\n<p>Unfortunately <code>RSCustomDaxFilter()<\/code> is not recognised by DAX Studio. However, there are workarounds that I will give in the final article in this series. Even more unfortunate is that adding <code>RSCustomDaxFilter <\/code>using Power BI Report Builder can also cause intermittent issues. So you are probably best advised to add this particular filtering approach to the DAX for the .Rdl file using Notepad++.<\/p>\n<h2>Some useful tips<\/h2>\n<p>I want to conclude this introductory article with a couple of practical tips that should help when starting out using DAX to query datasets for SSRS reports.<\/p>\n<h3>The SAMPLE() Function<\/h3>\n<p>Power BI datasets can become extremely large. The corollary is, inevitably, that running sample queries on industrial-sized data sources can take up valuable development time.<\/p>\n<p>One solution to ease the pain \u2013 and make queries run considerably faster when carrying out initial development &#8211; is the judicious application of the <code>SAMPLE()<\/code> function. For instance, you can simply wrap a DAX query in <code>SAMPLE()<\/code> as follows:<\/p>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk\">EVALUATE\r\nSAMPLE\r\n(\r\n10\r\n,SUMMARIZECOLUMNS\r\n(\r\nDimGeography[CountryName]\r\n,DimCLient[ClientName]\r\n,\"Total Sales\", SUM(FactSales[SalePrice])\r\n)\r\n,DimCLient[ClientName]\r\n)<\/pre>\n<p>The <code>SAMPLE()<\/code> function merely requires a number as the first element (the number of sample records that you want the query to return) and the DAX query as the second element. Of course, you have to remember to remove <code>SAMPLE<\/code> (and the final right parenthesis) once development is complete to allow accurate unit testing. The entire output from this piece of DAX is:<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"327\" height=\"219\" class=\"wp-image-92830\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2021\/11\/word-image-13.png\" \/><\/p>\n<h3>Dummy measure<\/h3>\n<p>If you are returning wide lists, that means DAX queries that contain many fields from different tables (whether or not the data is filtered) using <code>SUMMARIZECOLUMNS()<\/code>. You can easily find yourself facing long query times or even timeouts. The simple solution to resolve this challenge is to add a calculated value (directly calculated or using an existing measure) at the end of the <code>SUMMARIZECOLUMNS()<\/code> function \u2013 even if you will <strong>not<\/strong> need to display the calculation output in the actual report.<\/p>\n<p>You can see this technique applied in the following code sample:<\/p>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk\">EVALUATE\r\nSUMMARIZECOLUMNS\r\n(\r\n\u00a0DimVehicle[Make]\r\n,DimVehicle[Model]\r\n,DimGeography[CountryName]\r\n,DimGeography[SalesRegion]\r\n,DimGeography[Town]\r\n,DimCLient[IsCreditRisk]\r\n,DimCLient[IsReseller]\r\n,DimCLient[ClientName]\r\n,DimVehicle[MakeCountry]\r\n,DimVehicle[VehicleType]\r\n,DimVehicle[IsRHD]\r\n,DimVehicle[ModelVariant]\r\n,\"DummyMeasure\", COUNT(FactSales[SalePrice])\r\n)<\/pre>\n<p>The output from this piece of DAX adds an extra column. However, the increase in the processing speed more than compensates for any increase in data size \u2013 and you do not have to use the <em>Dummy<\/em> column in your paginated report.<\/p>\n<p>It is worth noting that the data output slowdown does not seem to occur when returning multiple fields from a <strong>single<\/strong> table in the underlying dataset.<\/p>\n<p>The future articles in this series contain many more hints and tips that you could find helpful when facing more complex challenges.<\/p>\n<h2>Next steps<\/h2>\n<p>Armed with what you have read in this article, you should be able to create paginated reports that you can either load directly into the Power BI Service or use when creating Power BI visuals using the Paginated Report visual in Power BI Desktop.<\/p>\n<p>However, there is still a lot to learn when using Power BI datasets and DAX as the basis for paginated reports. The next step is to extend the basic approaches that you saw in this article with more advanced DAX techniques that enable you to filter the source data for the paginated reports that you want to deliver. This is the subject of the next article.<\/p>\n<p>&nbsp;<\/p>\n","protected":false},"excerpt":{"rendered":"<p>SSRS reports can be built using DAX. In this article, Adam Aspin explains how to get started using DAX to build the reports.&hellip;<\/p>\n","protected":false},"author":2181,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143528,53],"tags":[5134],"coauthors":[12351],"class_list":["post-92822","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\/92822","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=92822"}],"version-history":[{"count":9,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/92822\/revisions"}],"predecessor-version":[{"id":93052,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/92822\/revisions\/93052"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=92822"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=92822"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=92822"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=92822"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}