6 Killer Power BI hacks for your next report

21 December 2021

6 Killer Power BI hacks for your next report

Whether you’re beginning to dip your feet into the world of Power BI, or looking to take your skills to the next level, we’ve compiled a list of tips and tricks to help hone your skills and make your reports intuitive, accessible, and reliable. Below, we’re tackling:

  1. Dynamically hiding objects 
  2. Adding a web URL link to a specified table 
  3. How to create navigational bookmarks 
  4. How to create custom tooltips 
  5. Optimising the mobile layout of your reports 
  6. Creating a dynamic TopN filter. 

To learn more about these tips, and to begin incorporating them into your reports, read more below:

Dynamically hide objects

Dynamically hiding visuals is a great way to make your reports easier to interpret, removing information that isn’t necessary while emphasising that which is. It also encourages your users to interact with the report, encouraging experimentation and giving them independence in accessing insights.

While there are many use cases for this feature, we’ll be using it to hide a State Bar Chart until a Country has been selected to showcase specific geographical data.

How to do it

1. The first step in this trick is to create 3 new measures:

First, ensure that ‘Country’ is filtered so that it returns 1 or 0. This is a new Measure in Power BI called ‘Check Filtered Country.’

You then need to change the ‘Title Colour’ of the visual that you’re manipulating so that it returns a font colour of black if ‘Country’ is selected, or transparent if not. This is also a new measure which needs to be created called ‘Change Colour State.’

We’ll do this same process for the shadow colour of the visual – changing the ‘Shadow Colour’ to return a colour of Grey if a country is selected, or transparent if not. This is also a new measure that needs to be created called ‘Change Shadow State.’

2. The next step in configuring these is to change both the ‘Change Title Colour’ and ‘Change Shadow Colour’ measures to a Text Value.

3. Once these measures are correctly formatted, we’re going to apply a new filter to the ‘State Bar Chart’ using the ‘Country is Filtered’ column to equal ‘1.’

4. We also need to amend the Title Font Colour on the ‘State Bar Chart.’ Use conditional formatting (the FX button) to apply a rules option referencing the ‘Change Title Colour’ column as below, do the same process for the shadow colour as well.

5. You should be all good to go now. You can test these results by selecting and unselecting a Country. You can see the results from our report below – the space remains empty for the viewer until they click on a country, such as the United States, which then produces the ‘State Bar Chart’ to appear.

Adding a web URL to a specific table is a great way to make your reports more accessible, interactive, and provide insights on demand. Whether it’s a URL link to another report, document, product page, or more, including a link button is easy and hassle-free.

How to do it

1. To begin, create a new measure with the website address. Depending on the setup of the database, this could already be an existing field. It’s important here to change the Measures ‘Data type’ to ‘Text’ and change the Measures ‘Data Category’ to ‘Web URL,’ as shown below.

2. Add the new measure that you’ve created to the table by dragging it across. You can see below that there’s now a ‘Web URL’ column providing these links.

3. Once this row is in place, the final stage is to resolve the issue of the links appearing too prominently. This may be an even worse issue if the URL is a particularly long one. Fortunately, fixing this is easy. To do so, go to the ‘Format’ tab of the Visualisation and open the ‘Values’ section.

4. Scroll down until you find the ‘URL icon’ option, change this to ‘On’. You can see below that those long URLs have now been replaced with a handy icon.

Using bookmarks to navigate a Power BI Report is a useful tool for your users and has a wide range of applications for easy accessibility. Don’t be afraid to be creative with this tool – for this guide, we will be using bookmarks to navigate filtered versions of the same page. It’s important to note that to take advantage of this trick, you must first update your desktop version of Power BI to at least November 2021.

How to do it

1. For our use case, we are first going to add the Country field to the page level filters.

2. Once done, open the bookmark pane by selecting it under the ‘View’ tab.

3. Without making any other changes to the dashboard, click the ‘Add’ button on the bookmark pane and rename it to all. This will give you a bookmarked page with all of your filters unselected.

4. Use the Country filter in the left-hand pane to select your first area of focus, then repeat the process of step 3 to add another bookmark, this time name it after the Country (in this case Australia).

5. Repeat the process in step 4 until you have a bookmark for each Country under the filter.

6. Under the ‘Insert’ tab, select the ‘Buttons’ option and then ‘Bookmark Navigator’ under the Navigator Option.

7. Position the Bookmark Navigator on your report, be careful not to obscure any important visuals.

8. You can now use the bookmark navigator to switch between bookmarks.

Creating custom tooltips

Creating custom tooltips for your PowerBI report allows you to include more insightful information when specific fields are hovered over, such as additional statistics or even more visuals. There are no specific prerequisites to include this, so get started today. For our example, we’ll be inserting a column chart to be displayed when hovered over.

How to do it

1. To begin, add a new page to the report and name it ‘Tooltips.’

2. In the newly created page, go to the ‘Format’ pane (The one that looks like a paint roller).

3. Click on ‘Page size’ and set it to tooltip.

4. In the ‘Format’ pane, go to ‘Page Information’ and set the Tooltip option to ‘On.’

5. Under the ‘View’ tab, select ‘Page View’ and set it to ‘Fit to page.’

6. As we’ve said, we’re going to insert a column chart for this example, but you can add any visualisation/table. In the column chart, we’ll add ‘StateName’ as the Axis and ‘SalesAmount’ as the value.

7. Return to the Template page and highlight the visual.

8. Go to the ‘Format’ pane and set tooltips to ‘On.’

9. Under the tooltip option, set the type to ‘Report Page,’ selecting our previously created ‘Tooltips page.’

10. Your visual now has custom tooltips that you can interact with on-demand.

Optimise the mobile layout

Optimising the mobile layout of your reports can help decrease miscommunication, encourage a more data-driven approach, and facilitate the use of your reports by making them easily accessible on any platform.

How to do it

1. To optimise your report’s mobile layout, begin by clicking the ‘View’ tab and then the ‘Mobile layout’ button.

2. Drag and drop components from the ‘Page visuals’ pane onto the mobile canvas, except for any background images.

3. Once done, resize and order the visuals as you see fit to make the report more mobile-friendly.

Pro Tip: Chart sizing is dynamic and they scale accordingly, yet as text boxes do not scale with resizing, any changes need to be made to the ‘Master’ visual.

5. Bring up the ‘Selection’ pane on the toolbar.

6. On the ‘Layer order’ tab in the selection pane, highlight the background image and move it to the bottom of the list. This will bring the background image to the back of your report.

7. Your mobile report is now complete. It’s a good idea to always test it out to make sure that settings are confirmed, and to troubleshoot any additional ideas as well.

With many years of experience in implementing intuitive Power BI solutions, we’re well equipped to enhance your current data-driven intelligence. Learn more here. 

Create a dynamic TopN Filter

To create a slicer to dynamically filter by a selected number (such as top 5 highest-grossing states), you’ll first need a report that allows you to enter a data table manually, so check your report’s configuration first before attempting this trick.

How to do it

1. Click the ‘Home’ tab and then the ‘Enter data’ button.

2. Create a table called ‘TopN’ and a column called ‘Top’ with the range that you’d like to dynamically filter by. The range could be in increments of 1, 5, or 10 – the choice depends on the need. For the first part of this example, we will be entering 1-10 in increments of 1.

3. For the example we are looking at ‘SalesAmount’ by ‘State,’ so we need to create a ‘SumSales’ measure on the Fact table (FactInternetSales) by using the following DAX expression SumSales = sum(FactInternetSales[SalesAmount]).

To create a measure, right-click on your chosen table and select the new measure option: this will bring up a field that allows you to enter your DAX expression.

4. Following the same steps as in step three, create the following measures on the TopN table:

a. SelectedTopNNumber = IF(HASONEVALUE(‘TopN'[Top]),Min(‘TopN'[Top]),10)

For this SelectedTopNNumber measure, the HASONEVALUE checks if the user has selected a value or not.  If they have, then use the number they’ve selected, otherwise set the default value to 10 (You can set this to be dependent on the range you created in step 2).

 

b. Store Rank by Selected Measure = IF(HASONEVALUE(DimGeography[StateProvinceName]),rankx(ALLSELECTED(DimGeography[StateProvinceName]),[SumSales],,0))

For the Store Rank by Selected Measure, it again checks if the Dimension StateProvinceName has a value. If it does, it ranks these values in that Dimension based on the SumSales measure.

 

c. Should Store Be Included = IF([Store Rank By Selected Measure]<=[SelectedTopNNumber],1,0)

For the ‘Should Store be Included’ Measure, Power BI checks if the ‘Store Rank by Selected’ Measure is less than, or equal to, the SelectedTopNNumber.

5. Click the ‘SalesAmount by StateProvinceName’ chart and then add the ‘Store Rank by Selected’ measure to the visual level filters and set it to = 1.

6. Click the ‘SalesAmount by StateProvinceName’ chart and then add the ‘Store Rank by Selected’ measure to the visual level filters and set it to = 1.

7. Add the ‘Top’ field from the TopN table to the Slicer’s Field.

8. Next, simply change the slicer type from ‘Between’ to ‘Dropdown.’

Accelerate your Power BI journey today

At DataShapa, we’re committed to empowering enterprises to realise the full potential of their data. From enhanced data visualisations to establishing a single source of truth with secure data engineering, we’re passionate about undertaking projects designed to continuously deliver value and achieve both short and long-term goals.

If you’re interested in learning more about how to boost your reports or to resolve some of the most common challenges facing users today, why not pick up our free Power BI guide today?

Get In Touch

We’re here to help. For any questions or enquiries you may have, get in touch with us here and one of our industry experts will respond as soon as possible.

Or call us direct on +44 (0) 20 3633 4510