This post is a quick review of Will Thompson’s “Create Impactful Reports with Power BI Desktop” session from the 2016 Data Insights Summit. His alternate title was “15 Weird Tricks That’ll Make Your Reports Awesome.”

He committed to posting the workbook he used for his demo, and so he has. The tips and tricks are here https://t.co/x3bLgsNBjm, and the report on player stats from the video game “Destiny” is here: https://t.co/2f7ia3lXbS.

Why do the product marketing folks in Redmond build most of their demos on topics that have nothing in common with every day business scenarios?

The first 10 minutes of the session was devoted to a Power BI Desktop demonstration, focused on cross filtering and some basic report formatting capabilities.

Be sure to provide your feedback on Power BI at http://ideas.powerbi.com.

Will’s Tips:

1. Selective Cross Filtering: In a report, use the format menu, edit interactions command to set the filtering behavior of each visualization in your report. Perhaps you want to have some of the visuals cross filter, while others remain in a high level context. This would be particularly useful in a financial report where visuals on a single report are grouped by multiple periods, e.g. fiscal month, quarter and year.
2. Sort by Column: Power BI sorts things alphabetically by default, which is a problem if your chart should be ordered “Pre-K, Kindergarten, First, Second…”. To order month names, days of the week, process stages, etc. appropriately, you’ll need a column in the dataset that provides an integer value with the correct ordering. Select the field in question (month, day of week, stage, grade…) in the fields list, go to the modeling menu, sort by column command and pick the ordering column to specify the sort order.
3. Dynamic Color in Visualizations: use the Color saturation setting of a visual to correlate color saturation with a value in the data. For more control, use the format pallet (click on the paintbrush over to the right side of the screen), and in the data colors area, specify a color for the minimum, center and maximum values in the spectrum. You can also set integer (why not floating point?) minimum and maximum values for these ranges. This would be useful in creating key performance indicator (KPI) type visuals, with green/yellow/red coloring. This capability is nicely documented here.Here is a report of the City of Austin, Texas employee statistics with a couple of visuals. The top is average hourly wage by department, with the color saturation indicating length of service with the city from green to teal to gray. Looks like the Office of the City Auditor is the right place to be a newby.The bottom visual is average annual salary by “Staffing Level”, with color saturation (I just dragged and dropped here, I didn’t use the format pallet) indicating average length of service in job.Yes, I realize it’s an epic ugly chart, but I don’t have all day to make things pretty for you people.
4. PowerPoint Integration: DevScope.net has a PowerPoint add-in that’ll bring in a PowerBI tile to your PowerPoint presentation and keep it refreshed.
5. Last Refreshed On: Add a custom measure to your dataset indicating when the data was last refreshed.
   1. Add a query called “LastRefresh” with the value
      =DateTime.Date(Datetime.LocalNow())
   2. In the report designer, add the custom measure to that LastRefresh query with this DAX Formula: “Last Refreshed On: ” & FIRSTDATE(LastRefresh[Date]).
   3. Place that measure in a card visual on your report, turn off label and maybe increase the font.
6. Reference Lines: In the format pallet of a visual, use the reference line area to add a visual threshold to your visual. Just a single reference line today I’m afraid. This needs to express mean, median, sigma, etc… and Will stated that dynamic properties are in the backlog. Vote them up.A work-around is to add a custom measure that returns a single value, use the combo chart, and drop the custom measure into the line values field.
7. Title Formatting: Change the background color of your titles to tie the report together, and to highlight the groupings of various visuals.
8. Alignment: Control/select to select multiple visuals, then use the align command on the format menu to align them.
   Another approach to sizing is to use the general area of the format pallet. That gives you complete control over X Position, Y Position, Width and Height of a visual.
9. Dynamic Text: DAX measures can return text. Use this capability to create dynamic, instructional titles. The DAX formula uses nested if statements and “HASONEVALUE” and “LOOKUPVALUE” functions to conditionally return text based on user selections within the report visuals.This terrific demo starts here: https://youtu.be/d2bZpNZ6uIA?t=27m55s.
10. KPI Tile: Provide a metric and a date, and this new visualization will show the value and value over time. You can also supply target goals to show traditional KPIs with this visual.
11. Copy/Paste visuals from one report to another.
12. Scatter charts with background visuals: You can now add an image to the plot area within the format pallet on a scatter chart.
13. Aspect Ratio: Change the page size within the format pallet of a report. This is a workaround for the fact that scatter chart bubble size is not controllable.
14. Best size for Power BI pinned reports to fit the tiles: in the format pane set them to 260*150, 260*365 or 555*150 (from Will’s twitter).
15. Cortana integration: Set the page size to be compatible with the Cortana pane in Windows.
16. Getting cities in the right place on a map: Combine fields together to disambiguate the location for Bing Maps. Create a new column, concatenating city, state, country… like this: Column = [City] & “, ‘ & Maps[Country]. Another option is to add the longitude and latitude fields. Lat/long handling was improved significantly in March, 2016.
17. Custom Visuals: Download custom visuals from https://app.powerbi.com/visuals/ and use them just like any other Power BI visual.

Great session Will Thompson, and great product!