Drillthrough from Paginated Report to Power BI

— report recipe
also see: Drillthrough from Power BI to an SSRS Paginated Report

In the recipe titled “Drillthrough from Power BI to Paginated Report” I demonstrate how to use report parameters and a dynamic URL address to navigate between reports and pass parameter values in the URL from a Power BI report to detailed data in an SSRS paginated report. Using a similar technique, we can navigate from a paginated report to a Power BI report.

Power BI is very flexible and does not require you to define parameters like you would in a paginated report. You can literally filter a report using any field in the dataset.

Product Versions

SSRS/Paginated Reports (one of the following):

· SQL Server Reporting Services 2016 or newer

· Power BI Report Server

· Paginated Reports in the Power BI service

Power BI Desktop:

· Power BI Desktop May 2019 or newer

What You’ll Need

· A Power BI report connected to a data source using import, DirectQuery or SSAS direct connect mode.

· Paginated Report server: SQL Server Reporting Services (Standard or Enterprise Edition), Power BI Report Server or a Power BI subscription with Premium capacity

· Moderate level Visual Basic.NET coding skills

Difficulty level: 300

The sample database and files will be available in the forthcoming book: SQL Server Reporting Services Paginated Report Recipes, 2nd Edition (working title).
These instructions are provided as an example but refer to files that will be available when the book is published. Please contact me in the comments with questions and feedback.

Preparing the target Power BI Report

You don’t have to do anything special in the Power BI report design to enable parameterized filtering; you simply need to know the tables and field names you will need to use for filtering. Most of the effort is building the dynamic URL in the paginated report.

The first order of business is to make note of each field or measure to use for filtering. I suggest jotting them down in a text editor using the notation: TableName/FieldName.

Note: Filtering is applied at the report level, so navigating to a report using URL filters will affect all the pages in that report.

In the following examples, I will demonstrate my experience with a report deployed to my Power BI tenant. In the exercises a little later, you will deploy a sample report and work through these steps in your own Power BI environment.

Addressing Power BI Objects

There are several URL-addressable objects in the Power BI service to which you can navigate using a URL. Each of these objects is assigned a globally unique identifier that can be addressed in a URL. Reports can be filtered, and dashboards cannot, but other objects that are related to reports can be filtered and will respond to a filter directive in the URL. For each object type listed in the following table, navigate to the object in your web browser and copy the URL from the address bar.

Object Type

Accepts Filters

Description

Dashboard

No

Dashboards don’t accept filters

Report

Yes

Shows default report page with report-level filters

Report page

Yes

Applies report-level filters to page

Bookmark

Yes

Bookmark and filter directives can be combined in the URL query string using “&” (rather than “?”) to append directives

Workspace app

Depends

Default page in app navigation must be a report rather than a dashboard

Workspace app report page

Yes

Same as a report. Use path to app or a report within the app

Workspace app bookmark

Yes

Same as report bookmarks. Filter directive may be added to the query string using “&” rather than “?”.

Filter Directives & Syntax

The URL query string filtering syntax for Power BI is borrowed from the OData standard which offers a great deal of flexibility. For example, depending on data types; you can specify that a field is equal to, less then, greater than, contains, in a range, or includes a list of parameter values. The filter directive is simply tacked onto the end of the URL specifying an object path in the form of:

?filter=Table/Field eq value

Text and date type values are passed in single quotes:

?filter=Table/Field eq ‘value

Multiple filters may be appended together using the “and” operator. This example uses real table and field names:

?filter=Product/Brand eq ‘Contoso’ and Sales/Quantity lt 5

This will filter all pages on the report to include only records where the Brand field in the Product table is “Contoso” and the Quantity field in the Sales table is less than 5.

All of the available filter operators are listed in the following table.:

Operator

Description

eq

Equal to

ne

not equal to

ge

greater than or equal to

gt

greater than

le

less than or equal to

lt

less than

in

in list (item1, item2, item3)

Before I can add the filter directive, I need to get the report URL. If I go my Power BI service, navigate to a workspace and then click to open a report, I can capture the URL from the address bar. As an example, here’s the address I captured for a report published in my Power BI tenant:

https://app.powerbi.com/groups/cdae7f3c-b818-493a-af56-55308d41798f/reports/0dcf5a4d-1bf2-438f-b13b-95eb9adadd91/ReportSection

You can see that each object type (“groups”, “reports”, etc.) is proceeded by a globally unique identifier; which is a long string of characters generated by the service. These identifiers don’t change after the object is deployed or created in the service.

Notice that the ReportSection element at the end of the address has no value which means that I have not specified a report page. If I were to use this address to navigate to the report, Power BI will show me the default page, which is the page that was visible when the report was last saved. If I navigate to another page and then capture the URL, the object ID of that page is appended to the “ReportSection” text at the end of the address:

https://app.powerbi.com/groups/cdae7f3c-b818-493a-af56-55308d41798f/reports/0dcf5a4d-1bf2-438f-b13b-95eb9adadd91/ReportSection3b93b5700b15a516ed98

Note: The syntax for the page or ReportSection is a little different than other objects and does not use a forward slash before the object ID.

The common terms we use for some of these objects are a little different that the attribute names used in the URL query string, as noted here. I will show you some additional object names after this first example.

Object Type

Query String Attribute

Object ID

Workspace

groups

cdae7f3c-b818-493a-af56-55308d41798f

Report

reports

0dcf5a4d-1bf2-438f-b13b-95eb9adadd91

Page

ReportSection

3b93b5700b15a516ed98

Now, to add the filter directive to the report URL… When I append the filter directive from the earlier example to the report URL like this…

https://app.powerbi.com/groups/cdae7f3c-b818-493a-af56-55308d41798f/reports/0dcf5a4d-1bf2-438f-b13b-95eb9adadd91/ReportSection?filter=Product/Brand eq ‘Contoso’ and Sales/Quantity lt 5

…the report opens with only data where the Brand is Contoso and Sales records have a quantity less than 5. But, my browser seems to have mangled the URL making it difficult to decipher:

https://app.powerbi.com/groups/cdae7f3c-b818-493a-af56-55308d41798f/reports/0dcf5a4d-1bf2-438f-b13b-95eb9adadd91/ReportSection?filter=Product~2FBrand%20eq%20%27Contoso%27%20and%20Sales~2FQuantity%20lt%205

This is a necessary practice when passing certain information in a URL called “escaping”. There are certain characters that either have special meaning to a web server or simply can’t be passed in a web URL without special encoding. Spaces and forward slashes are the most common examples that must be converted to a special string of characters so that a web server (in this case the Power BI service) understands what a web client (in this case, my web browser) is asking for in the web address and query string directives. Technicalities aside, we must substitute these characters with special “escape character” strings. Cryptic as it might seem, this is simply a requirement.

Report URL on Power BI Report Server

If you are using Power BI Report Server rather than the Power BI service, you can get the report URL in the same way. Simply navigate to the report and capture the URL in the address bar. You will not see object IDs like those generated by the Power BI service but just the named folder and report. For example, here is the path to a report named Sales Analysis in a folder named Report Recipes on my local report server:

http://localhost/reportspbrs/powerbi/Report%20Recipes/Sales%20Analysis

The pattern for adding filters to a report published to an on-premises report server is the same as the Power BI service. Append the filter directive query string parameter to the URL using the same syntax (beginning with “?filter=”).

Handling Special Characters

If any object names that contain literal spaces or other special-purpose characters (like % or @ for example), you must explicitly replace these with properly escaped notation. In the previous example, you saw that the web browser replaces forward slashes with “~2F” and spaces with “%20” within the URL.

This is where things might get a little confusing but it’s really not complicated. The HTTP escape character encoding that your web browser does automatically uses a different syntax than the Power BI service or Power BI Report Server. For object names (tables or fields) in the filter directive, Power BI employs a different syntax called Unicode URL escape codes, which is a seven-character string beginning with “_x” and ending with “_” and a four-character hexadecimal number in the middle. To represent a space, the code is “_x0020_”. Use the following table for reference:

Character

HTTP Escape Code

Unicode URL Escape Code

Space

%20

_x0020_

$

%24

_x0024_

&

%26

_x0026_

`

%60

_x0060_

:

%3A

_x003A_

<

%3C

_x003C_

>

%3E

_x003E_

[

%5B

_x005B_

]

%5D

_x005D_

{

%7B

_x007B_

}

%7D

_x007D_

%22

_x0022_

+

%2B

_x002B_

#

%23

_x0023_

%

%25

_x0025_

@

%40

_x0040_

/

%2F

_x002F_

;

%3B

_x003B_

=

%3D

_x003D_

?

%3F

_x003F_

\

%5C

_x005C_

^

%5E

_x005E_

|

%7C

_x007C_

~

%7E

_x007E_

%27

_x0027_

,

%2C

_x002C_

Let’s say that I need to specify a filter for a field named Product Category in a table named Sales Orders, and I only want to see data for two specific categories, Accessories and Bikes. Before adding escape codes, the filter string would look like this:

?filter=Product/Product Category in (‘Accessories’,’Bikes’)

If I replace the necessary characters (within object names) with Unicode URL escape codes, the filter string becomes:

?filter=Product~2FProduct_x0020_Category in (‘Accessories’,’Bikes’)

Additionally, my web browser automatically adds HTTP escape codes for spaces the quotation characters outside of object names, which produces this string:

?filter=Product~2FProduct_x0020_Category%20in%20(%27Accessories%27,%27Bikes%27)

In summary, you do not need to apply the HTTP escape codes because your browser will do that for you. If you capture the URL with the escaped characters, that’s fine. However, you must replace spaces and special characters with the filter directive object names with the appropriate Unicode escape codes.

Now that you understand the syntax of the filter directive and the need for escape codes to replace certain characters and spaces, you are equipped to build a fully-qualified URL for the report and folder.

Build the Report Navigation Solution

In this solution, you will make enhancements to a fairly standard SSRS paginated report. You will use a custom Visual Basic function and expression to encode a dynamic report URL with parameters to filter a Power BI report.

Design the Source Paginated Report

A starting report is provided for you. This is a simple SSRS paginated report connected the SSRS2019Recipes sample database.

1. Make a copy of the Sales Summary-Starter.rdl file and rename it Sales Summary.rdl.

2. Open Sales Summary.rdl in Report Builder (shown in Figure 1).

clip_image002

Figure 1

3. Open the data source and make sure you can connect to the SSRS2019Recipes database on you SQL Server. Change the server name in the connection of necessary and test the connection.

4. Leave Report Builder open to resumereport design work after the following steps.

Open and Publish the Power BI Target Report

1. Open Power BI Desktop and then open the Sales Analysis (Drillthrough Target).pbix report file.

2. Publish the Sales Analysis (Drillthrough Target) report to a workspace in your Power BI tenant or to a folder in you Power BI Report Server.

Note: If you are using Power BI Report Server rather than the Power BI Service, you must use the version of Power BI Desktop that installs with Power BI Report Server. To publish a report in Power BI Desktop for report server, choose Save As… from the File menu. To publish a report to the Power BI service, choose Publish from the File menu.

3. Navigate to the published Sales Analysis (Drillthrough Target) report in your web browser, shown in Figure 2.

4. Select the entire URL in the address bar and copy it to the Windows clipboard.

clip_image004

Figure 2

Continue Designing the Source Paginated Report

Now that you have the complete URL for the published Sales Analysis Power BI report in the clipboard, you can return to Report Builder where you were previously editing the Sales Summary SSRS paginated report.

1. In Report Builder, select the report parameter named PBIReportURL.

2. Right-click the selected parameter and choose Edit Parameter from the menu.

1. Select the Default Values page.

2. Click to select the Specify values radio button.

3. Click the Add button.

4. Paste the URL for the Power BI report into the Value box as shown in Figure 3.

5. Click OK to accept the parameter changes.

clip_image006

Figure 3

Create a Custom Function to Encode Object Name Text

1. In Report Builder, right-click the report designer background outside of the report body (dark gray area) and select Report Properties from the menu.

2. In the Report Properties dialog. Select the Code page.

3. In the Code box, enter the following Visual Basic.NET code.

Note: The custom code editor has no syntax validation or debugging features, so it can be challenging to debug and correct errors in your code. I recommend that you challenge yourself by hand-entering this code but if you have any trouble, a copy of this code is provided in the Code.txt file for your convenience.

Function EncodeObjectNames ( ObjectNames as String ) As String

Dim sText As String = ObjectNames

‘– Replace Spaces —

sText = REPLACE( sText, ” “, “_x0020_”)

‘– Replace $ —

sText = REPLACE( sText, “$”, “_x0024_”)

‘– Replace & —

sText = REPLACE( sText, “&”, “_x0026_”)

‘– Replace % —

sText = REPLACE( sText, “%”, “_x0025_”)

‘– Replace ‘ —

sText = REPLACE( sText, “‘”, “_x0027_”)

Return sText

End Function

The completed function is shown in Figure X

4. Click OK to accept the code changes and close the Report Properties dialog.

clip_image008

Figure 4

Add Link Icon

1. Select the Insert tab in the main Report Builder application.

2. From the Insert ribbon, click the Image button.

The mouse pointer changes to an Image icon.

3. In the table, click the right-most cell in the Detail column to drop the new image.

The Image Properties dialog opens.

4. On the Image Properties dialog, click the Import button.

5. Drop down the file extension filter list which is currently set to JPEG files (*.jpg).

6. Select the option to show all file types: All files (*.*).

7. Browse to the correct folder and select the Link Icon.png file.

clip_image010

Figure 5

Add Navigation Action

1. Select the Action page in the Image Properties dialog.

2. Under Enable as an action:, click to select the radio button labelled Go to URL.

3. Click the “fx” button to the right of the Select URL: drop-down list.

clip_image012

Figure 6

4. In the Expression dialog, enter the following code.

Note: Be particularly mindful of the literal single quotes surrounding the ProductSubcatory field reference at the end of the expression.

=Parameters!PBIReportURL.Value

& “?filter=” & Code.EncodeObjectNames( “Product/Product Subcategory” )

& ” eq ‘” & Fields!ProductSubcategory.Value & “‘ and Date/Year eq ” & Fields!Year.Value

5. Click OK to accept the new image settings.

6. Save your changes to the Sales Summary report.

7. Switch to Preview and test the drillthrough navigation link by clicking the link icon on any row.

clip_image014

Figure 7

The target report should show only data for the selected Subcategory and Year.

8. Deploy the Sales Summary paginated report to your report server or Premium Power BI tenant.

9. Test the deployed Sales Summary report to verify that it works as it did in the designer.

Drillthrough from Power BI to an SSRS Paginated Report

— report recipe
also see: Drillthrough from Paginated Report to Power BI

Navigating from a Power BI report to an SSRS Paginated report with filters and parameters.

Product Versions
SSRS/Paginated Reports (one of the following):
– SQL Server Reporting Services 2016 or newer
– Power BI Report Server
– Paginated Reports in the Power BI service
Power BI Desktop:
– Power BI Desktop May 2019 or newer
All versions of Reporting Services accept parameters in a similar fashion, but subtle changes were made to URL parameter formats in SSRS 2016.  Some adjustments may be required if you want to apply these techniques to SSRS versions prior to 2016.
What You’ll Need
– A Power BI report connected to a data source using import, DirectQuery or SSAS direct connect mode.
Report server:
– Power BI Report Server or a Power BI subscription with Premium capacity (minimum: P1 or A4)
– Moderate level DAX coding skills  
Difficulty level: 200

Designing the Power BI Report

This recipe primarily involves Power BI report design techniques. I’m not going to get into the details of Power BI report design but will cover the basics with a partially-completed report to get you started. If you are less-experienced with Power BI you can use this as an example for future report projects.

The sample database and files will be available in the forthcoming book: SQL Server Reporting Services Paginated Report Recipes, 2nd Edition (working title).
These instructions are provided as an example but refer to files that will be available when the book is published. Please contact me in the comments with questions and feedback.

Solution

Figure 1 shows the Sales Summary Power BI report, which is deployed to my local Power BI Report Server along with the Sales Detail, a paginated (SSRS) report also deployed to my local Power BI Report Server.

clip_image002

Figure 1

We will start by looking at the target report. This is just a standard SSRS paginated report. A completed copy of this is provided for you to configure and deploy to your report server or Premium Power BI service tenant. The sample report is very simple and could easily be enhanced to include more information in greater detail.

1. Open the Sales Detail.rdl report in Report Builder

There are four report parameters in this report, two of which are used to connect to the SQL Server database. The other two parameters are used to pass filter values to the underlying query.

2. Edit the ServerName parameter and set the Default property to the name of your server or instance

The default value for this parameter LocalHost which connected to my local default instance of SQL Server. You can leave this setting if your SQL Server instance is the same as mine.

3. Close and save any changes the ServerName parameter

4. Preview the report and make sure it connects and displays data from the sample database

5. Select different values for the Year and Country parameters using the parameter dropdown lists and click the View Report button to see values in the report change, and to confirm that the parameters are working correctly.

6. Use the Save As… option on the File menu to save the report to a folder on your report server or a workspace in your Premium Power BI tenant

Note: Depending on the Report Builder menu option and deployment target, the terms “save” and “publish” are used interchangeably, meaning that the report has been deployed to a server or service where it can be ran and viewed by users.

Get the address for the published paginated report

1. Open Notepad or your favorite text editor

2. In a web browser, enter the address for your report portal or Power BI portal where you published the Sales Detail paginated report

Tip: If you don’t know the address for your web portal or report server, you can use Report Server Configuration Manager to lookup the Web Service URL and Web Portal URL. By default, the web portal address is http://SERVERNAME/Reports and the report server address is http://SERVERNAME/ReportServer (where SERVERNAME is the name of the server where Reporting Services is installed) but this is configurable and might be different in your environment.

3. Navigate to the folder or workspace and run the newly published report, verify that the report runs and returns data

4. Use Ctrl-C to copy the full address for the report from the browser address bar to the Windows clipboard

5. Paste the address into the text editor

The next step can seem a little tricky but it’s not really complicated. You need to get the path to the report on the report server rather than the report portal.

If you published the paginated report to your on-premises report server, do the following:

1. In your text editor, copy and paste the report address to a new line

2. In the new address line, locate the text “Reports” and change it to “ReportServer”

3. Copy the modified address line to the address bar in your browser and press Enter

The browser should navigate to a text menu page similar to Figure 2.

clip_image004

Figure 2

The address for the report is actually not yet in the correct format but it was sufficient to navigate to the Home folder on the report server. The following steps are used to obtain the correctly-formatted report address.

1. Use the links displayed on the report server menu page to navigate to the folder to which you published the Sales Detail report and click the report name link to run the report

2. When the report is displayed, copy the complete address from the address bar and paste it to a new line in your text editor

This is the correct path for this report on the report server. You can new add parameter values to the end of the address.

3. Append parameters and values to the address in the address bar like this. Each parameter is preceded with an ampersand character:

&Year=2018&Country=Canada

4. Press Enter and verify that the parameter is changed in the report

Open the starting report in Power BI Desktop and add drillthrough navigation for the paginated report

1. Make a copy of the Sales Summary-Starter.pbix file and rename it Sales Summary.pbix

2. Open Sales Summary.pbix in Power BI Desktop

If you will be using Power BI Report Server to deploy report on premises, make sure that you use the version of Power BI Desktop designed for your server version. If in doubt, you can download and install Desktop from the download menu in your report web portal.

3. On the Home ribbon, click the Edit Query button to open the Power Query editor

4. You will see three query parameters. Two parameters are used to manage database connection information in a similar manner to the Sales detail paginated report.

Tip: Using parameters in the Power Query editor can help manage variable information like connection information, file paths and addresses.

In the starter report file, there are three text type query parameters. Modify these parameters, using the following instructions:

Parameter NameDescription
ServerNameSQL Server address or server name. You can use “LocalHost” if it is a local instance.
DatabaseNameSSRS2019Recipes
ReportURLPath to the deployed SSRS/paginated report

5. If necessary, change the ServerName parameter current value to connect to your SQL Server

6. Copy the report server address for the deployed Sales Detail report from your text editor and past it into the current value ReportURL parameter.

Build the dynamic target report address link as a measure in Power BI

The ReportURL Parameter value is exposed as a column in a table named Constants. You will use this to build a dynamic link in the report.

1. On the Home ribbon in the Power Query editor, click Close & Apply to process queries and return to the report designer.

2. Figure 3 shows the report designer in Power BI Desktop. The objective is to add links to each item displayed in the table visual, allowing a user to see a paginated report with details related to a selected year and country.

3. Expand the Sales Summary Subcategory Country table in the field list on the right

clip_image006

Figure 3

4. Click the ellipsis (…) to the right of table name to see the context menu and select New Measure

5. The formula bar is displayed above the report canvas with placeholder text: Measure =

Note: You can use the down arrow on the right side of the formula bar to expand the formula editor window, and the up arrow in the same location to collapse the formula editor

6. Replace the placeholder text with the following new measure:

Detail Report Link =
VAR SelectedYear = SELECTEDVALUE( 'Sales Summary Subcategory Country'[Year] )
VAR SelectedCountry = SELECTEDVALUE( 'Sales Summary Subcategory Country'[Country] )
VAR RptURL = SELECTEDVALUE( 'Constants'[Report URL] )

RETURN
RptURL & "&Year=" & FORMAT( SelectedYear, "0000") & "&Country=" & SelectedCountry

The purpose of this measure is to build the address text used for our link to the detail paginated report. The SelectedYear and SelectedCountry variables get those respective values. The RptURL variable gets the report path the parameter. This DAX script assembles the URL in the appropriate format.

As you enter the text, use the Intellisense and autocompletion features offered by the editor to build the DAX code. These features can help with proper code syntax and save you from typing errors.

7. Press Enter to validate the code and save the measure

8. With the measure selected in the field list on the right, switch to the Modeling ribbon

9. Use the Data Category drop-down to select Web URL (see Figure 4)

clip_image008

Figure 4

Add report links to the report

With the Detail Report Link measure added to the data model and categorized as a web URL, you can add a link to the table that will dynamically generate a context-specific link for report navigation.

1. Before you go any further, click the floppy disk icon on the toolbar to save your work

2. Select the table on the right side of the report canvas that shows sales by product category grouped by years

3. Drag the new Detail Report Link measure from the field list to the Values field well below the SalesAmount field. This adds a new column to the table visual showing a long URL in blue “link” text (see Figure 5)

clip_image010

Figure 5

Tip: It is easy to deselect a visual so before you begin changing properties, make sure that you still have the table visual in the report canvas

4. In the left-most blade in the panel to the right of the report canvas, click the paint roller icon to see the properties for the table visual, shown in Figure 6

clip_image012

Figure 6

5. Expand the Values group

6. Locate the URL icon property and switch the state of the property to On

7. Verify that the web addresses displayed in the last column of the table are replaced with a small blue link icon, which you can see in Figure 7

clip_image014

Figure 7

Test and publish the completed report solution

A this point, navigation should work from the report in Power BI Desktop, just as it should after the report is deployed.

1. Hover over the link icon on a few different rows to see the parameters at the end of the address in the tooltip

2. Test the report navigation by clicking a link after noting the corresponding year and country.

3. Publish or save the paginated report to your report server or Premium Power BI tenant

4. Test drillthrough navigation in the deployed reports

5. Navigate to the deployed copy of the Power BI report

6. Locate a row with values for a specific year and country and click the corresponding link

The paginated report opens in the browser

7. Verify that the paginated report parameters reflect the selected year and country

clip_image016

Figure 8

Final Thoughts

Report navigation is the essence of data exploration and gives users the ability to understand their data in proper context. Dashboard and summary reports help business users see the big picture but having access to details let’s them understand the low-level details behind the numbers. Power BI as an ideal tool for summary-level, interactive reporting and paginated reports are great for consuming detail-level data. Using this technique allows you to have both of these capabilities. Power BI reports can be published to your on-premises Power BI Report Server or to the cloud Power BI service. Paginated reports do not have to be in the same location and can be deployed on-prem in a standard SSRS report server, Power BI Report Server or in you Premium Capacity Power BI Service tenant.

A couple of things to keep in-mind when using this technique are that there are string size limits when passing parameters in a web URL. You should avoid passing sensitive information in clear text parameters. When navigating between reports located on different services or services, users may be prompted for their credentials to login to a service.

Power BI Performance Analysis

How can you find the cause of slow performance issues in Power BI? This post demonstrates some of the advanced tools you can you to test slowing performing queries and to do deep troubleshooting and performance analysis in your data models. Using the new Performance Analyzer in the Power BI Desktop May 2019 release, finding the cause of slowly-performing reports is easy.

The Performance Analyzer allows you to see the time it takes to run DAX queries for each visual and slicer on a report page. It also shows how long it takes to render the visual and for “other” activities such as web service calls and waiting for other blocking visual queries. After viewing the timings for each visual, you can export all the results to a JSON file for comparison and trend analysis.

The Performance Analyzer will be my first step for analyzing report performance. DAX Studio has several advanced features that are useful for performing deep analysis, troubleshooting and DAX measure development. It is a tool that requires more investment and some advanced knowledge to get beyond the surface of simple issues, but has more advance query tracing, development and problem-solving features.

Tell me about your experience. Please post your comments about your experience using the Performance Analyzer. What other techniques and tools are you using to find and troubleshoot performance?

Advanced DAX Training from Marco Russo in Portland: June 2019

It’s very rare to have the opportunity to learn DAX from the world’s leading expert but we will have that opportunity in Portland on June 19-21! The one-and-only Marco Russo is coming to Portland to teach The Italians’ Mastering DAX course. No one knows DAX like Marco and his ability to teach others about simple, advanced and uniquely insightful concepts is truly remarkable. DAX is simple but it’s not always easy. You can create some amazing calculations and analytical expressions with DAX but many advanced concepts allude most users without help learning some challenging concepts. If you need to learn DAX to use in Power BI, SSAS Tabular or Excel Power Pivot – or to take your DAX Skills to the next level, do not miss this training event.

While in town, Marco will also be the keynote speaker at the 2019 Pacific Northwest Power BI Symposium on June 18th. Last year’s event was a big success. We enjoyed hosting great speakers and a large number of local attendees last year and this event will be just as great.

Getting Started with Power BI Report Design level 101 – What Every New User Should Know

Let’s step through a few very simple things that every Power BI new user needs to know. Power BI is a great tool and it’s fairly easy to get started but there are a few gotchas that everybody experiences. It’s easy to get frustrated with little minor things until you master a few simple features in the designer. I’m just going to walk you through just a few of these items. It doesn’t matter how simple or how complex the project or your data model is, or what your data looks like. What I’m going to show you are some things that everyone experiences.

The first thing is that Power BI like most business intelligence tools really exists for the purpose of grouping and aggregating data. Now, that’s not to say that we can’t use it for detail-level reporting – and we can work with a lot of data but by default, it wants to group and it wants to aggregate. Sometimes that’s not what we want it to do.

I have a table here that I’ve just created, and this is just to simplify things but I’m going to work with a single table. You would experience the same thing if you are working with multiple tables in a data model. As you can see, I have a name column with the name of some of my my favorite cartoon characters. I have a location and I have a numeric column that I want to report on. We’re going to call that my measure or my implicit measure column. it’s just a column of numbers and just keep in mind that we’ve got three records for Fred two records for Barney two for Wilma etc and notice that those are in combination with different locations now I’m going to switch back to a blank report page and I would like to see the number of shells that are sold to a customer at a location and so I’m going to drag and drop the name column on to my report canvas and that’s going to create this table of names. The first thing that I noticed is the text is very small so I want to bump up the text size. I’m looking up here… looking at all of my ribbons for a place to bump up the text and I can’t figure out how to do that. The first thing is that you need to make sure that you have this visual selected. This is a table visual – there you can see that that’s a table and I can move that around and it can get a little confusing because when the table is already selected, and I go to grab it, that it doesn’t always move.

If you click off of the table and then hover over it, and grab it with the left mouse button; then I can move it around but that might seem to be a little inconsistent. I can also resize it but this white table over the white background… it’s hard to see where that table is. I want to bump up the text so I’m going to go to the paint roller. You can see that this is the format option. It’s simple – once you’ve done it a few times. With this table visual selected, when I choose the paint roller, I get to the formatting options. I’m going to go down to “Grid” and here you can see that I can bump up the text size. So, I can just spin that up to let’s say 14 points. That’s a whole lot easier to read. Because this is a unique combination or unique values in that column, I’ve only seeing four of them right now. I’m going to add location. Now, I can drag and drop into the visual.

With the visual selected, if I’m looking at the field list, I can drag and drop into what we call the “field wells”. This is the field list (on the right of the panel)… these are the field wells for the selected visual (on the left of the panel). Now you’re seeing the unique combination of name and location. I’ll drag Shells field into the field wells and notice that for Fred, I only see two rows. If I’m looking at the rows for Fred, how do I know if these are detail rows or if they’re being rolled-up? Now, we go back and we look at the table and we can figure that out pretty easily. If I’m just coming into this data for the first time, I may not be able to tell how how many rows or records there are in the underlying tables behind this visual. How can I figure that out so that I can validate this data and make sure that my data is accurate? Well, the the best thing that you can do is get a row count. There are a couple different ways to get a row count. If I were just to drag and drop one of these fields that I’ve already used into the visual, I can use it to count rows. I drop this down and I can say “let’s count that”. I don’t want to count it distinctly… I just want to count it and that gives me a row count because it is the count of the number of occurrences of location. Here you can see that this row in the table visual actually represents two records in the underlying table and so that kind of solves this puzzle for me by letting me know that 104.08 is actually the sum of the shells for two underlying records where the name is Fred and the location is Bedrock. There are two records that represent that one row in the table visual. Okay… so that that solves mystery number one.

Now another thing that I can do is drag and drop location onto the report canvas. Then I can switch this to Count and that just gives me an overall count for the entire report. I’ll switch that to a card visual whitch makes that a little bit bigger, so now I can reconcile the number of rows that I see here (which is six) against the number of records in my table (which is 8), knowing that that there’s at least one row here that consists of two or more underlying records in the table. That’s an easy way to resolve that.
Alright, item number two is how do I know where these visuals are on the page? I see that I’ve resized this visual and there’s whitespace all over the place and that’s not very intuitive. What I like to do in new reports is to choose the background of the report canvas, go to the paint roller (make sure that we’re looking at page information) so you should see that I have this property group called “Page Background”. Again, that’s because I just clicked on the background of my page. I’m going to go to the page background and I’m going to set the background color to a light shade of gray. I notice that it doesn’t change and that’s because by default the background of a page has a 100% transparency. why? I don’t know but if I change that, you can see that it is now a very light shade of gray. You see that my table also is transparent so I’m going to go to the paint roller and I’m going to turn the background on which does show me a slightly different background color. The default background is going to be white. I’m just going to double check that it is white. I’ll set the background transparency to 0%. I prefer to see solid colored visuals sitting on a light grey background because now I can see where things are and it’s much easier for me to differentiate where those visuals start and stop. That goes a long ways toward helping my users understand what they’re seeing on this report.

What if I didn’t want to roll-up the name and location to a single row on this table? How can I see my detail records regardless of whether the name and location are the same or not? The fact of the matter is that Power BI is always going to group on any fields that have the same value – so you have to have a unique column. You have to have a unique value that you can group on and that is the purpose of this Unique ID column. Before I just show you that I can use that Unique ID to show detail rows, I want to show you something that happens with any numeric column by default in Power BI. Numeric columns are always automatically set to summarize. That’s what this little Sigma icon is just to the left of this field. When you see that. We do want the shells column to roll-up and summed but not the Unique ID column; we want to be able to group on this Unique ID and use it to make sure that we get our detail rows. I need to come over here to the modeling tab and I’m going to drop down the “Default Summarization” list and set this to “Do not summarize”. That gets rid of that little Sigma icon and now if I have my table selected and then when I drag and drop that field… (oh, look at that it’s going to give me a count by default so it’s even overriding my my summarization). I have to tell it not to summarize right here in the field list. I’m actually getting one row in the table visual for every record in the underlying table and that’s exactly what I wanted. Unfortunately, there’s no way to to hide or get rid of this unique ID column. If I didn’t want, I can just make that column wide and I can resize the table and get rid of that the header text.

Hopefully, this helps dispel some some very simple quandaries that a lot of new users struggle with just a little bit. Best of luck as you’re getting started with Power BI!

Data Geeks and Data Jocks Meetup in Vancouver, Washington

Our little “Data Geeks” Meetup group in Vancouver, Washington is growing with a line-up of heavy-hitters and bigger crowds.

This month, on April 16th, we are honored to welcome Reid Havens, Microsoft MVP and Business Intelligence superstar from the Seattle area. Reid will be presenting “Demystifying Chart Types & Report Design Principles”. Reid is the Founder of Havens Consulting Inc. He has a formal background in technology and organizational management, having obtained separate Master’s Degrees in Organizational Development & Business Analytics. He has experience working with many of the Fortune 50, 100, & 500 companies. Additionally he guest lectures at the University of Washington and other universities, teaching Business Intelligence, Reporting, & Data Visualization.

Please RSVP on the user group Meetup page for the event. Thanks to Lexicon Solutions for sponsoring this month’s meeting. Please visit their site if you would like to upgrade your career or are seeking technical talent.

We’re seeing considerable cross-pollination of complimentary user groups in the greater Portland area which gives professionals in the region several quality, free learning and networking opportunities every month. These groups include the Oregon SQL PASS Chapter, which meets downtown Portland on the second Wednesday evening and the Portland Power BI User Group, which meets on Beaverton on the 4th Wednesday evening of each month. With the Vancouver Data Geeks meeting on the 3rd Wednesday evening, if you attend these data-centric user groups three out of four Wednesday evenings each month, you will be smarter than you can imagine and should have immense career opportunities!

In February, we had international trainer and well-known conference speaker, Peter Meyers share his Power BI insights to a crowd so large that we had to find a larger venue. Indeed, our Southwest Washington technical community is growing. Little did we know that if you search the Meetup site for ‘Data Geeks’ in Vancouver, you might find the “Vancouver Data Geeks” group in that other Vancouver way up north of the 48th parallel. We didn’t plan that nor did they but it has a been a minor point of confusion. We may be the smaller of the Vancouvers but we are making a name for ourselves, even in the shadow of Portland, Oregon; our big sister city to the south, just over the river.

Thank you all for participating in our growing community and we look forward to seeing you on April 16th for Reid’s session at the Interject Data Systems Office in Salmon Creek, right off I-5, Exit 7A.

Azure Everyday Roundup

Short (2-4 minute) video posts to help you learn about the Microsoft Azure analytics and data platform.

In November of last year, I joined the Pragmatic Works consulting team as Principal Consultant where I work alongside several great professionals, many of which I have known and have worked with over the years. One of the many things I appreciate is the deep expertise and collaboration we share across this diverse team of professionals. In early 2018, we began sharing a short video post every single day in a series called “Azure Everyday”. I’ve contributed several of these video posts in the past couple of months that I’d like to share with you here. I encourage you to check out the complete collection of Azure Everyday recordings using the link at the end of this post.

Each video post is a short insight or learning about a unique capability in Microsoft Azure. These short videos are a great way to become familiar with all of the Azure services and new features in the Microsoft cloud data platform.

New Features That Help Manage Large Volumes of Data

Data Governance with Power BI Premium

Azure Data Lake Storage to the Rescue!

Modeling Options for Enterprise Scale BI Solutions

Paginated Reports Integrated into the Power BI Service

To see the entire collection of Azure Everyday video posts, visit this page on the Pragmatic Works blog site.