Fun with Microsoft Power BI - Part II - Reporting

By Tony Lee

Welcome to part II in this series of going from zero to hero using Power BI to ingest, process, and make amazing reports.  If you have read some of our other articles you can probably tell by now that we enjoy making data actionable. Honestly, it doesn't matter what type of data or even where the data ends up. As long as we can make informed decisions using the data -- we love it. Following in this theme we are going to make BlackBerry (formerly known as Cylance) Protect Threat Data Report (TDR) CSVs actionable using Power BI and Power BI Desktop. You can use any data source to follow along in this series, but our example BlackBerry Protect report is shown below which we will happily share the Power BI file at the end of the series for you to load and analyze your own data, so stay tuned for that!

Figure 1:  Our Power BI report using BlackBerry Protect TDR data

In the first article, we covered:

• Getting Started
• Data Ingest
• Adjusting Fields
• Visualizations
• Saving Your Work

In this article, we will cover:

• Tabs
• More Visualizations
• Text box
• Slicer
• Table
• Pie Charts
• Treemap
• Using Reports and Dashboards
• Uploading Reports to Power BI Service (Online)

Tabs

Just a quick note for folks that are familiar with excel tabs. In Power BI, they work much the same. Since the canvas size is somewhat fixed in size, tabs might come in handy for building multiple reports and overcoming canvas limitations.

Figure 2: Shows boundaries and tabs

More Visualizations

In the first article, we showed you how to add an event count, which essentially counted the number of rows in our BlackBerry Protect Threat Data Report. We started off with an easy visualization and will progress to more difficult ones.

Text box

We are fans of being able to quickly glance at a dashboard and know what it is...  thus a simple text box at the top is a great way to accomplish this task.  Insert > Text Box.  Then get fancy fancy with the background color and font. We will get even fancier in a later article using images with hyperlinks, but let's crawl, walk, then fly.

Slicer

We are also fans of being able to interact with the data by carving and drilling down into interesting events. For this, we use a slicer (Power BI term), also known as a filter to the rest of the world. ;-) In our BlackBerry Protect example, we are filtering on multiple fields using a checkbox in most cases. To do this, simply click in the canvas, click on the slicer icon, drag the field you want to filter on, and bob's your uncle. 

Figure 3:  Check box filters (aka slicers)

You may have also noticed that we have a time range selector in our report. This is also a slicer, but we dragged a time field into the field box and Power BI is smart enough to convert it from a check box to a time range selector. For our example, we used the "Create Time" field for the malware. Note that there are additional options that specify how the time range selector behaves. Those are configurable by using the down arrow. Our preference for this scenario is "Relative Date" which allows you to specify a relative time range such as:  Last, Next, or This: X days, weeks, months, and years.

Figure 4:  Configuring how the time range selector works

Table

Tables are fairly straight forward to use as well. To use, simply click on the canvas > click the table icon > Drag the fields you want in the table.  The down arrow allows you to correct the order of appearance if necessary by moving the field up and down the list or you can drag the box up or down in the list.

Doughnut and Pie Charts

We saved doughnut and pie charts to later in this article because it is not as straight forward to create it depending on your data set. We used a doughnut chart in our example for "File Status" and "Detected By". The problem with these fields is that the data source does not provide the statistical breakdown (percentage per file status or what detected the malware). That must be calculated and Power BI may or may not do that automagically. For this, it helps to know a little bit of DAX to create measures. Per Microsoft:

"Data Analysis Expressions (DAX) is a library of functions and operators that can be combined to build formulas and expressions in Power BI, Analysis Services, and Power Pivot in Excel data models."

"Measures are dynamic calculation formulas where the results change depending on context. Measures are used in reporting that support combining and filtering model data by using multiple attributes such as a Power BI report or Excel PivotTable or PivotChart. Measures are created by using the DAX formula bar in the model designer."

Source:  https://docs.microsoft.com/en-us/dax/ 

These formulas are heavily dependent upon functions and there are a lot of them. A good reference is found here: https://docs.microsoft.com/en-us/dax/dax-function-reference

As an example... say you had a column called "Sales Amount" and you wanted to sum that and store it in a measure called "Total Sales".  The DAX for that would look like the following:

Total Sales = SUM([Sales Amount])

Now that we have a bit more understanding, we will create the "File Status" doughnut chart. To do this, we went to the Modeling menu > New measure > and enter:

File Status Stats = DIVIDE(count('ThreatsDataReport'[File Status]),CALCULATE(count('ThreatsDataReport'[File Status]),ALLSELECTED(('ThreatsDataReport'))))

Note:

• ThreatsDataReport is our table, the entries in the brackets [] are the columns
• ALLSELECTED is so our slicers (filters) still work when selected

Once we click the checkmark, we should have a new field called "File Status Stats".  We will click the canvas, click the pie or doughnut chart icon, and drag the "File Status Stats" field to the Values box in visualization and we will drag "File Status" to the Legend box.

Figure 5:  Creating a measure and a doughnut chart in Power BI

Doughnut and Pie Charts - Cont.

Note: After some trial and error, we discovered a potentially easier way to create doughnuts and pie charts showing top values. Select either pie or doughnut chart and drag the field into the Legend box and drag the same field into the Values box and make sure Count is selected for Values. This creates the same pie and doughnut chart as our DAX fanciness above.

Pro-tip:  You can now change this visualization to a table to show an easy to read statistical breakdown (Top or Rare).

Figure 6:  Create a pie or doughnut chart while avoiding DAX

Treemap

Fortunately, creating a treemap is similar to creating a doughnut or pie chart. Once you have the DAX (or logic) created, it is all downhill from there. In our example, the malware classification data made an awesome treemap visualization. For this treemap, you can either drag the same field twice (with Value as a Count) as we showed in the example above, or you can use DAX for the calculation.

Our DAX to calculate the classification stats measure:

Classification Stats = DIVIDE(count('ThreatsDataReport'[Classification]),CALCULATE(count('ThreatsDataReport'[Classification]),ALLSELECTED(('ThreatsDataReport'))))

To create the treemap, click the canvas, then the treemap icon, drag the "Classification" field to the Group box, and the "Classification Stats" field to the Values box.

Figure 7:  Creating a treemap using the malware classifications

Note: Most visualizations apply a filter when clicked. Not all of them are very intuitive in how to clear the filter. For the treemap, either click the box you already clicked or click the top left box to clear the filter.

Using Reports and Dashboards

Now that you have a report built, you can use it as-is in Power BI Desktop. The slicers function to carve up the data as needed, however there is no presentation view or mode. Thus your presentation to others will not look as polished as it could. To get around this problem, we can take the .pbix file and upload it (or publish it) to a personal workspace in Power BI Service where you can use it as individual reports or pin visualizations from multiple reports to make a dashboard.

Uploading Reports to Power BI Service (Online)

Upfront warning:  Be careful publishing the work since the .pbix contains your data set as well and could potentially leak information.

Keep in mind that there are multiple methods to load a local Power BI Desktop file into the online service. You can use the Publish button in Power BI Desktop or you can log in to your Power BI account:  https://powerbi.microsoft.com/en-us/landing/signin/

Click "My Workspace" > "Get Data" > "Local File" > navigate to your .pbix file

Figure 8:  Uploaded .pbix to Power BI Service workspace

This should now be in your personal workspace and you can view the report via your web browser. To enter a full screen presentation mode, click the double arrow in the top right corner of the window.

Figure 9:  Entering full screen presentation mode

Conclusion

In part II of our series we covered how to use tabs and many more visualizations--including some that are not so straight forward. We only scratched the surface of DAX since that could be a blog series unto itself. But we also discovered the presentation limitation in Power BI Desktop and how to over come that presentation by using the Power BI Service. We hope that you are now able to take data, ingest it, create a report, and present that report in a professional setting. Our future articles will focus on improving the usefulness and functionality of carving and displaying data using Power BI. Please feel free to leave feedback and your favorite Power BI features in the comments section below. Stay tuned for more great Power BI info!