After 14 years using Microsoft On Premise BI Tools (SQL Server, Reporting Services, Integration Services and Analysis Services) Its time to embrace Business Intelligence in the cloud.
Currently we have lots of visuals on the report pages and the only way to know that they drill through to other reports is to hover over them
In the March 2020 release there is new functionality in the form of buttons
Add a blank button
This button is now set to drill through preview and you have chosen the drill through page. On normal drill through, everything this that is available is shown which is difficult when the report structure is complex. This allows you to target and manage the drill through structure.
Nothing selected 31 days selected
And this drills through to the detail reports for 31 days. Note that the tooltip in Desktop tells you how to drill through. Lets see this in Service.
Change the text on the button
Lets customise the button text using conditional formatting
the data item behind this drill through is ‘Resolved in’ and then it contains number of days categories.
Create a new measure
A new measure has been added to the dimension table containing the Resolved in Item
String for Resolved in Button logic = IF((SELECTEDVALUE('dim table'[Resolved in ],0)==0),"See Resolved in Details", "See Details for " & SELECTEDVALUE('dim table'[Resolved in ]))
If the selected value is null, set it to 0. So if its zero, show “See resolved in Details”. If its not zero then it can be displayed
Add New Measure to the Button
Resolving a noisy drill through with Drill through Buttons
This visual in this Drill though report has three Drill through’s for Incident. Then the stage of the incident. Finally the Business Property Drill through. All are valid but we would prefer to Not have the stage here because this should only be accessible via Incident.
Add two buttons. Link one to the Incident Drill through report. The other to the Business Property Drill through report
We want the button to be dark red when its active.
Create a new measure for the Incident in the incident Dimension
String for Incident in Button logic = IF((SELECTEDVALUE('dim BPIncident'[Incident Ref],0)==0),"See Incident Details", "See Incident Details for " & SELECTEDVALUE('dim BPIncident'[Incident Ref]))
And add this as conditional Formatting to the button Text
Create a new measure in the Business Property Dimension
String for Business Property in Button logic = IF((SELECTEDVALUE('dim BusProperties'[BP Number],0)==0),"See Business Property Details", "See Business Property Details for " & SELECTEDVALUE('dim BusProperties'[BP Number]))
And add this as conditional Formatting to the button Text
And now you should get drill throughs that the user can immediately see and use. Avoiding the stage drill through because it doesnt match what we want the journey to be
Note that the buttons are greyed out
Weaknesses
The button takes up more space.
Currently you have the drill through button and the drill through on the visual. This could be confusing to the user. It would be great if you could turn the drill through on the visual to off
When creating Power BI reports you will be working with
multiple pages and may want to include a menu for each page. We can also go
further and set up toggles to display your data in different ways
To create Power BI reports that are more user friendly and
interactive we need to understand Bookmarks
Cost Of Organised Crime UK 2010 2011.pbix (Example Power BI
Desktop File)
horr73-tabs.xlsx (Example File to be used with the Power BI
report)
The Report contains a few pages of very basic reports
because I want to focus on bookmarks, buttons and toggles
I have created 6 reports. Choose some data that you are interested in and set up numerous reports
Setting up Bookmarks
I want to add buttons on each page, so the user can click
quickly to each report. To do this we
need to set up bookmarks for each page.
My first page is called A1 1. Go to the page and click on View to select Bookmarks pane
With the correct page selected click Add and I’m calling this bookmark A1 1. Because you have created it with the A1 1 page (1st page selected) this is now your book mark for Page 1
Go to each page an and set up a new bookmark
Now you can click on Each bookmark and note that you are seeing the right report against each bookmark
Choose your buttons
You can go about this a few ways. We can add images or Buttons. For this example, I am going to use images with different borders to denote on or off
For your first page choose a relevant image (Or button and add text)
Position your button / Image on the page
Do this for every page until you have a button for each page on page 1
Once you are happy with the positioning copy and paste each
button / image to the exact position on each page
This will mean rearranging your pages slightly to
incorporate the menu
Connect your button to a bookmark
Each Button needs connecting to a bookmark. For Page 1, we do not need to do anything to Page 1 icon because the user is on Page 1. Go to icon 2
In format image, set Action to 1. Connect it to the correct
bookmark.
I have also added a tooltip over the image
Repeat for all the other images on page 1 (Apart from the
image for page 1)
Once completed go to page to and repeat for every image on
page 2 (Apart from the image for page 2)
At the end of this, all the buttons on every page should be
active and connected to the correct bookmark
APART from the button for the page you are on which should be inactive
Format the Inactive Button on every page
You can’t test the buttons in desktop but imagine you are
clicking to different pages. How do you know which button is inactive because
you are on that page? You need to distinguish the different between active and
inactive buttons
Go back to page 1. I have used an image and there are other
ways of doing this. If you are using a button. Or if you want to use another
image etc.
I am simply going to replace each image with an image to
show that you are here.
On page 1. Delete the inactive Page 1 Logo and replace with
the new logo
Do this for every page
You could also change the back ground, add a border etc.
Test out your buttons in Service
I am going to use my Workspace in the service. In Home, go
to Publish and Publish to My Workspace (Or whichever app workspace you want to
publish too)
Go to your workspace and Reports and click on the new report
that you have made
Click on each button to make sure it links to the correct page
Tidying up the reports
You can now get to each report via your buttons. Note that each report also has a tab at the bottom of the screen. Only want the user to click on the buttons. Not the tabs as it may get confusing. Go back to desktop
Right click on Page 2 and choose hide page. Do this for every page apart from page 1
Page 1 will become our main page so I’m going to rename this
Main
Republish you report and go back to service
Note that you now can’t see the tabs for the other reports
Toggles
You report is looking good but for one of my pages I want to
show the data in a different format and I want the user to be able to select
the way they look at the data
For this I need images of toggles which I have downloaded
This is slightly more complex than setting up the bookmarks
for each button. Go to the report that you want to change and add a new
bookmark. I’m calling it A1 10 Tables
Its time to open up the selection view.
Note that for the original bookmark, the selection shows you everything on this book mark. I need to go to the new bookmark and hide everything that’s specific to the original page
I would also like to mention here that you can also now Change the name of the item in the selection bar so all your images can relate to the image in the report, rather than just displaying card.
This was a feature added in a later Power BI Update.
In the new Bookmark click on the icon against the objects on
your report to hide
I have hidden 3 objects.
(The current design makes it almost impossible to
distinguish Hidden from none hidden objects which I’m hoping they change)
https://ideas.powerbi.com/forums/
265200-power-bi-ideas/suggestions/36265075-selection-pane-icons
I am going to add a table to this new book mark
now I have cleared space
Note that the table is now in the selection.
However, if you go back to the original bookmark the table
will be in here too overlapping with all your other visualisations.
Simply select the first bookmark. Hide the table. Right
click on the first book mark and update. You can now flip between the two bookmarks
to see it move from visualisations to a table
Add your toggles
Finally, we need to add toggles so we can use them to toggle between bookmarks. On Bookmark one I need to add space for the toggle. I’m also adding some text
Now go to the second bookmark. Notice that you can see the
toggle and text. They will need hiding but in the meantime. Add the second
toggle image and make sure it’s the same size as your other toggle
With the first toggle in place, resize the 2nd
toggle and then place this toggle over the top of the first toggle. Add your
text
Go back to bookmark one and hide the new toggle and text.
Remember to Update the Bookmark.
Go back to the new bookmark and hide the 1st
toggle and text item. Click to each bookmark so you are happy with what you can
see
Bookmark 1
Bookmark 2
Connect up your toggles
Now its time to connect your toggle. Bookmark 1 needs to go
to Bookmark 2 and Bookmark 2 needs to go to Bookmark 1
In Bookmark 1. Select the 1st toggle. Same as before. Under
Format Image, switch Action to on. Set to Bookmark and select Book mark 2
Repeat for Bookmark 2
Test your Bookmarks
Go back to the first report. Save and publish your updated
report
Debbies Microsoft Power BI, SQL Fabric and Azure Blog 