If you must use Power BI Report Builder....

Development

My brief encounter with Power BI began after one of the project managers had the idea of using Power BI Report Builder as a means for generating archived versions of what’s displayed on a site I developed not long ago, because he wanted a PDF export feature for that. ‘Why not just copy a couple of the API methods and add a PDF converter to them?’, I wondered, with the feeling I’d end up needing to do that anyway.

It turned out another team and several engineers had already attempted this, and gave up. I had a lot more success and produced something approaching a working solution, but came to the same conclusion: Report Builder is the wrong tool for the job.

Power BI is an analysis tool for Excel people and data analysts who typically do Excel things and know more about DAX than a SQL admin/developer would care to know - hence the terminology I used here might be incorrect.

The main problem, I soon discovered, is Report Builder not only fetched everything from the data source, but created a row for every permutation of the records plus those they’re mapped to in other database tables - we’re talking literally millions of rows, if everything we need is added to the main report, and without adding a filtering parameter to the DAX query itself. A cursory search on the Web told me this is a common problem, and I do offer a partial solution here.

Loading Data into PowerBI Report Builder

The data layer of a PowerBI report is very analogous to that in a .NET application: There is a connection and a data model. In the left-hand window, these are labelled ‘Data Sources’ and ‘Datasets’. The ‘Data Sources’ folder will contain objects representing connections to databases and APIs - these should already exist in whichever PowerBI workspace is being used. The ‘Datasets’ folder will contain the model of the data tables that are loaded from the .pbix file.

The dataset will be empty until a query is added. Right-click the dataset’s name, then ‘Query…’. Each entry under that node represents a table within the data model - perhaps a database table. Field names from within each must be dragged into the main window to get the data. That’s how a very basic report is created.

Setting up a filter or query

Even in a basic report, the data would need to be queryable, so the report can show only the data for one specific record. There are two methods of parameterising a Report Builder report to view a selected one. This method selects rows from the data that’s already fetched from the dataset. The other method I describe later on - parameterising a DAX query - filters the data before it’s loaded into Report Builder, and that’s a partial solution to the data loading problem.

Setting up a query for the data that’s already loaded is a two-step process. First we need to add a parameter, which is basically an empty variable declared as a string, integer, etc.

Click ‘Parameters’ -> ‘Add Parameter…’.

The only section to worry about in the Report Parameter Properties window is the initial one. The Name and Prompt are the parameter name and the UI label for that parameter. Here I’ve set the parameter type as a string and allowed the value to be null.

The next step is to add a filter to the dataset and map it to the parameter that was just added. Right-click on the dataset, and select ‘Dataset Properties’. In the Dataset Properties window, filters can be added in the Filters window by mapping the parameter to any field in the dataset.

What can we do about the loading time?

The only solution I can find, after some digging, is to limit or trim the number of records Report Builder fetches before they’re loaded into the report. That involves playing with DAX. The thing is DAX is a data manipulation language, not a database query language, but I’m hoping we can do basic SQL-like operations with it.

Clearly the default query is pulling a vast amount of data. It does the equivalent of a SQL SELECT. It will look something like this:

EVALUATE SUMMARIZECOLUMNS('SitePages'[page_id], 'SitePages'[page_title], 'SitePages'[date_published], 'SitePages'[page_content], 'SitePages'[page_tags], 'SitePages'[related_content])

This will fetch all records and instances of them for each mapped record.

I’ve tried using the DAX equivalent of ‘SELECT * WHERE page_id = ‘’’. This uses the FILTER instruction. e.g.

EVALUATE FILTER (
SUMMARIZECOLUMNS (
'SitePages'[page_id], 'SitePages'[page_title], 'SitePages'[date_published], 'SitePages'[page_content], 'SitePages'[page_tags], 'SitePages'[related_content]
),    'SitePages'[page_title] = "My Site Page")

It still takes a good ten minutes to load, as even then it creates a huge number of rows representing all the relational data being included.

Filtering data by in-query parameter

The DAX query couldn’t access the parameter I already added for the report, so it had to be declared again in the query editor. When I added a parameter to the Query Designer window and run the report, the parameter appeared in the parameter box at the top. This is where the parameter that was added can be declared and mapped to a field in the report.

Add a parameter to the query so it looks something like this:

EVALUATE 
DISTINCT (
    FILTER (
        SUMMARIZECOLUMNS (
'SitePages'[page_id], 'SitePages'[page_title], 'SitePages'[date_published], 'SitePages'[page_content], 'SitePages'[page_tags], 'SitePages'[related_content]        ),
        'SitePages'[page_title] = @PageTitle
    )
)

The report will run much faster if relational data is loaded as subreports. The process for this is a little drawn out, partly because there’s a lot of parameter passing, and because Report Builder only allows subreports to be imported from a Power BI workspace.

Nothing I read online worked for me, so I’m hoping others would find this useful:

  1. In the sub-report file, set a parameter, under the ‘Parameters’ section. There’s nothing special about this, as it’s just a placeholder. Set the Name, Prompt and possibly the null and multiple vales options in the General tab. Don’t change anything in the other tabs unless you need to.

  2. In the Datasets section, right-click ‘Dataset Properties’, and go straight to the Filters tab. The Expression field is the primary key we want to search records on. The Value field should point to the parameter that was created. Now, when the subreport is run, it should display the records in which the primary key matches whatever was entered as the parameter.

  3. In the main report, we want to pass whichever of the main dataset’s values to the subreport as its parameter. Add a subreport, and point that to the subreport file. Now we want to pass one of the main report’s dataset values to it. In the ‘Change subreport parameters’ window, the parameter in the subreport should appear in the dropdown for the ‘Name’ field. After clicking the expression button, we can select the field to pass to it from the main report’s dataset.