Hidden Treasures in Power BI: A Baker's Dozen

We are not talking about a Rocky Mountain Forrest Fenn’s Treasure or even a mysterious Spanish ship lost in the desert. Rather, we are talking a plenitude, of often overlooked and completely inedible non-culinary Power BI features sitting right under our masked noses. While we would all like to think that we have a method to our madness, it is easy to get caught up in developing reports, visualizations and dashboards using the same methods each time. In a way, it makes sense. As one of my colleagues puts it, “While there are often multiple ways to do something in Power BI, there is no wrong way. You just do it the way you know how”. Even still, it’s nice when we can take time sharpening the axe, as Honest Abe would say, and find new, more efficient, possibly even entertaining ways to accomplish our tasks. With that, let’s look at some features in Power BI that are easy to overlook but that can save time or simply add new dimensions to our development skillset. We’ll start with, what we call: Main Street’s Baker’s Dozen (13th is on the house!) 1. Hide Un-needed Slicer Values The solution for hiding slicer values is to create two slicers (copy/paste). Then, hide one of them. The hidden slicer is the input to the second slicer. In this case, we will enable the Selection pane. It is in the Selection pane that you will hide a slicer that contains everything, but only has the desired selections checked.

2. Copy Visual Formatting Using Format Painter While we could always copy a visualization and plug in new fields in order to maintain formatting, sometimes we forget, or the idea for the formatting comes after the fact. Luckily, Excel and Word aren’t the only Microsoft applications that have a Format painter. If you have already created a visualization and would like to use formatting already in use on another visual, find your yellow paint brush on the toolbar and use the Format Painter.

3. Non-responsive Ribbon Sometimes after loading data, the ribbon looks unresponsive. No need to close and risk losing your work, just hit the minimize/maximize button. However, if the data is still loading, then it really is going to be a matter of patience. 4. Bookmark With Filter Pane Open Depending on your user-base, it may be helpful to show that there are additional filters available to interact with and/or to see what filters are being applied. In order to make the bookmark status a default: With the Filter Pane open, select View/ bookmark pane and select Add.

5. Formatting elements Within Cards As with bookmarks, this is where the Selection pane comes in handy. By selecting View and then enabling the Selection pane, you now have access to all the elements of the card: Text box, Card or KPI characteristics. 6. View Hidden Fields Often, and for a variety of reasons, there are hidden fields. When looking at the Fields panel, if you don’t see an amount or other field from a table that you’d like to use, Right click on the table and select “View hidden”. The hidden fields will then show and will display as grayed out.

7. The Timeline Storyteller If you haven’t seen the Timeline Storyteller custom visual, it might be time to try. It is a great way to tell your data story in a method that is easy to understand. You have the option to display logarithmic or relative scales, chronological lists with the duration of events or list dates and times in grid, spiral or circles, as well as custom shapes. You can create a linear list of dates or times, or lay them out in circles, spirals, grids or custom shapes. You can also show a chronological list, a sequence that shows the duration of events, or pick relative or logarithmic scales. 8. Create One Table to Store All Measures Although you can add measures to various tables, the measures themselves are independent of any table (i.e. It is not necessary for them to sit with the fields/amounts over which they were created). Wouldn’t it be nice to be able to look in one table for all your measures? To do this, we create a dummy table by selecting Home/Enter Data. Then type any text into the first cell and name this as “Measure table”. Select Load, Close & Apply. You will now have a Measure Table in your model which you can right click on to add New Measure. It is best to use a naming convention for measures where the first part of the name is the name of the main table over which it is created. Final step: Remember that this started as a dummy table with one column? From the Fields pane, you can hide that one column or, after adding at least one measure, go ahead and delete it. Finally, you can move existing measures by accessing the Modeling tab/Home Table. 9. Power Query/Display Options for Word Wrap and Mini Map Reading through the code can be tedious. Using the Display Options, you can easily Enable Word Wrap and Display Line Numbers can be a gift to your eyesight. Display Mini Map is particularly useful when you have a long list of code and could use the birds-eye view to locate a section of code without scrolling through it all. You will find these in Desktop from home/ Transform Data/Advanced Editor, select Display Options.

10. Sort Columns In Power Query, do you notice how new columns are added to the far right and then it is necessary to drag and drop to the desired location? You may already know how to sort alphanumerically from the Home tab, by selecting Choose columns and then selecting the A-Z icon to sort by name. Unfortunately, the side-effect of that is that an Applied Step is created called “Remove Other Columns”. That means that, if you were to add another column, it wouldn’t show. A neat trick to get around that is to take the steps above, but then take this extra step: Right click on the first column and select Move/To Beginning. You can now remove the step for Remove Other Columns. This will maintain the alphanumeric sorting without excluding new columns as they are added. New columns won’t be sorted, but at least they will show! 11. Conditional Formatting We can create visualizations with default color rules, i.e. Below zero show as red, above zero green.

In the Visualizations pane, select the formatting icon. Select Data colors and then select the function button (fx).

From the Format by dropdown, select Rules.

We will use many of the defaults that are already in the conditional formatting Rules section. The default reads “If value is greater than or equal to 0 percent and is less than 0 percent then (color)”. For the first Rule, we backspace over the first zero so that the word Minimum displays. Use the dropdown to select Number. Now, select the New Rule button. On the second Rule, we change the percent to Number and backspace over the second zero to reveal the word Maximum. Choose desired colors for each.

12. Copy Multiple Buttons That Have Actions (Ctrl+click) Often you’ll want to copy buttons from one Page to another, but you cannot select them all as you would in other applications and simply Ctrl C/ Ctrl V from within the visualization. Instead, open the View/ Selection pane; now you have the option to Shift/Click to select them all and continue with a Ctrl C/ Ctrl V onto a new page.

13. Toggles Oh yes! We are finishing with an On/Off toggle! When the icecaps melt and the ozone, becomes a distant memory, we will be toggling. Why? It’s fun, it looks cool, and it allows for viewing the same data using carefully selected visualizations. Also, there are times when we just don’t want our visualizations to venture onto two pages. We want to set the filter or Slicer once and have every visualization we need. A toggle is really a bookmarked button with action. Bookmarks capture the state of a visualization. So, we are going to layer visualizations and images right on top of each other, and then hide & bookmark. First, display the Bookmarks & Selection panes from the View tab. In this example, I’ve made a copy of a ribbon visualization, Ctrl C/Ctrl V, and changed the copy to a Donut chart. I then imported an image of an On switch and an image of an Off switch, and these are layered one over the other as well.

In the Selection pane we then, simply hide the ‘On’ image and the visualizations that correspond with On, and Bookmark it. We do the same for the ‘Off’ image and Bookmark it. Select Bookmark 1 (Off) in the Bookmarks pane. Click on the image for Off and the Format image pane will appear. Each bookmark is going to reference the other. We turn the Action to On and the Type will be Bookmark, but we will reference Bookmark 2. Repeat steps for Bookmark 2 but the action will reference Bookmark 1.

Now, when you Ctrl/Click, the visualization will toggle between the ‘On’ image and its associated visualization and the ‘Off’ image and its associated visualizations. Hopefully, you’ve found at least a couple of these tricks useful and enlightening. If you would like more information around any of these hidden treasures, please feel free to reach out. In turn, if you have any that you’d like to share, they will be very welcomed. - Joe Wolf Joe is an ERP Functional Analyst and Business Intelligence Solution Consultant with Main Street Apps.