Power BI – Toggle Button and Bookmarks

Hi there! So I had a really interesting
question come up this week and it was about the ability to toggle between a
relative date slicer and a date slicer with a range. The reason the question
came up is that we have a dashboard that contains a live tile that is pinned from
a report that uses a relative filter that is filtering for the current month.
So when that live tile gets updated a couple times a day the tile contains
information as the month progresses. The problem is that when users log in and
access that report they want to be able to select a specific date range in
addition to being able to look at the relative slicer for the last month or
the current month or the last three months. So it reminded me of a post that was done originally by “Guy in a Cube”. I think it was Patrick that went through
how to use a toggle button to change between two different types of charts. In
my case I wanted to toggle between a different type of slicer but also
preserve the way that the slicer was selecting its date range. That’s what
I’m going to take you through here. Now I’m going to just pull in some sample data from our our test server here. I’m just using the “AdventureWorks” database
so there’s a separate instance that we have here. That’s why I’m going to use
the slash in the server name and then we’ll pull in the AdventureWorks
database. It’s actually the data warehouse version. We’ll go ahead and
connect to it here I’m going to pull in a couple sample tables. Of course
we’ll use “DimDate” to pull in some of the data information. We’ll take “DimProduct” just to get some product info. Then “FactInternetSales” contains
the data of the actual sales. We’ll load that information in. When it loads I have
to go change the relationships that are inside the tables that it defaults to.
Specifically, the ones for the date because it’s going to pick up multiple dates. So let’s go over to the relationships here and I’m going to take off Ship Date.
I don’t want that relationship. I also don’t want Due Date right now
for this example. We’ll just leave Order Date. I’ll double click that line. We’ll make that relationship active, click OK and then go back to our report.
This isn’t really a report building exercise as far as charts go I’m just
trying to demo how the toggle button works. We’ll start out with a pretty simple chart here. We’ll take from the DimProduct table
the English Product Name. Then from the fact table, we’re going to take the
Order Quantity. That’ll be the value that we’re going to use. Just making sure
it’s summing, which it is. Alright, we’ll pull that over here in the middle and
then we’ll click the ellipsis (the three dots) here. And sort it by Order Quantity. That
looks good. Then we’ll go and add data labels to the end of these and see
where we’re at. Alright, that looks decent. I’m going to add
one other element to this particular report. We’ll add a card that just has the Order Quantity as a sum in here. Without any filters,
I’ve got 60,000 pieces of data in here. OK, now I’m going to add in a date slicer.
So let’s add the date, let’s add the slicer. Now I have to add the date. We’ll take the Order Date and we’ll drop it in there and in the data warehouse
version, I’ve got data from 2010 to the very beginning of 2014. I’m just
pulling in a date range slicer. I’m going to hit the drop down here and change this
to a relative slicer. Because I’m dealing with old data, I’m going to say I
want to look at the last four years and you’ll see the quantity there changes.
I’m looking at December of 2013 to December of 2017. Alright, so I’ve got
a relative slicer I’ve got the chart built. You can see in here I can
toggle between multiple different types of slices that I have
for dates. I’m going to save this. Then I’m going to publish it out and
show you what it looks like out on the Power BI Service, and show you where sort
of the problem comes in. I’m going to call this Toggle Button Example. We’ll
go ahead and publish this out to my Power BI Service, say here, I’m just going
to put it on My Workspace. We’ll let it publish it and then we’ll go ahead and
open it up and I’ll show you where sort of the issue comes in here with this
particular slicer. Let’s go up and pull it up. Alright it looks just like I
had before. You’ll notice though that that drop down button is gone. I have the ability to clear out the selection but I really can’t change the
way this looks unless I edit the report which I don’t want my end-users doing. So
going back to Power BI Desktop, I’m going to have to introduce a toggle button
here to get it to work. Let’s go ahead and drag this down a little bit. I’m
going to make this a little bit smaller. I’m going to go over and insert some
images in here. I have some stock images of toggle buttons that I’ve
downloaded off off the Internet. Let’s go ahead and get into my pictures
and just some app icons. You are free to use whatever ones you can find that
would work for you. These are the two that I found. I’ll have a toggle
button on that looks very similar to the way the iPhone toggles work. Alright, so
we’ll leave it like that, make it maybe just a touch smaller. That’s fine, then I’ll have a second toggle button. That’s the off button. We’ll go ahead
and pull that in and I’m going to mimic the size of this one. If I look at it
it’s 96 by 56. We’ll do the same thing over here, 96 by 56. That’s good. Now, if I
were to make these two things sit on top of each other like
that, they would overlap, one would hide the other one. They look about the
same size. Then I just want to add a text box here. I’m going to call this a
“Relative Date Filter”. I’ll select it, make it bold, get rid of
the light text type. OK, that looks fine to me. Then we’ll just
pull it next to what’s already there. Maybe pull this up just a little bit. OK, that looks good. These are a little bit big but for the example that’s OK. I’ve got my buttons in place. Now I need to handle the way that the dates
are filtering. I’m going to have one date that is relative and another date type
that is a date range. I really want to copy this box. I’m going to hit CTRL-C
and CTRL-V. I’m going to copy the way that this slicer works but I’m going to
hit the drop down here and change it to “Between” so it gives me this between
range. Alright, I’m going to leave those two the way that they are. Now, I have to add
the bookmarks and the selection feature. Before I do that, to make sure you
have it enabled, go to File Menu ->Options and then Preview Features. Make sure “Bookmarks” is checked if it’s not, check it, click OK. You may have to restart the
Power BI Desktop to get it to appear. Once it’s there, we’ll go to the View tab,
turn the Bookmarks pane on and turn the Selection pane on. Then I’m going to
hide fields and visualizations right now. Don’t need those. We’ll start
setting up the way the bookmarks work. What I really want to do is when the
toggle is on, meaning that the relative date filter is the one being shown, I
want to hide the between filter and the other toggle. What we’ll do is we’ll
select this toggle which is the off one and the between date filter and we’re
going to go over to the Selection Menu here and expand this just a little bit. You’ll notice that there’s these eyes, these eyeballs, these eye icons here
and I’m going to choose those to turn those things off. Then I’m going to click the add bookmarks button. The way that it
works is whatever the current state of your report is in is the state that it
will be bookmarked in that includes the date range that you have selected. I
have the last four years currently filtered, that’s what’s going to be saved in the
bookmark. If I don’t want that I need to change it now before I add the bookmark
or I’ll have to redo it over again. Last four years is what’s going to be
filtered. I’m going to click Add and then we’re going to rename this bookmark as
“relative on” because that’s the current state that it’s in. OK, I’ll
go back and click the Show All button in the selection frame here and
then we’ll select this toggle button, the on toggle button and the relative date
filter. Then I’ll go over to selection and I’ll hide those two. Remember the current state of the way my filter is set right now is what will be
saved in the bookmark. It’s going to give me the data for all time. If I
wanted it to be set to just a year I need to come over here and change it now
to something like that and then when I add the bookmark it will preserve the
way the filter is saved. Now this is an important distinction to make when
you’re saving these bookmarks because if you really wanted to look at just the
current month for that filter, so you pin the live tile then you need to
save it like that as part of the bookmark. Alright, so that bookmark is
saved I’m going to come in and I’m going to call this “relative off” because all
the relative stuff is on, the range is on. All the relative stuff is off and the
range on and the slider is off. OK, those two bookmarks are set. Now I
need to assign those bookmarks to the buttons. We’ll go back and we’ll show
everything again. Then I’m going to expand the visuals frame here, so that I
can format the image. What I want to do is when somebody clicks, a user clicks
the toggle button when it’s in the on state, I want it to actually trigger the off state because it’s being switched
off. Under the link options in “format image” I need to turn link on. Then
instead of it going back, like I’m drilling through, I’m choosing bookmark. Then I need to choose the bookmark that I want it to trigger and I want it
to trigger the relative off bookmark. Because if they’re clicking it when it’s
on that means they’re turning it off. Alright, go to the toggle that’s off, turn
link on for a bookmark, and then say that I want relative to be on because it’s
off so when they’re clicking it it’s going to turn it on. Alright, so let’s go
back over. I’m going to “Show All” but I’m going to
click one of the buttons while holding the Control (CTRL) button down because if I just click it now it’s selecting it, so click it while holding the Control (CTRL)
button and you’ll see that it hides that option. If I click the on toggle, the on
toggle disappears and the off one appears. The range now comes up if I
control click the off toggle it’s now turned on and the relative date range
now appears. You’ll notice that it preserved the way that the settings were.
So watch this, if I go back and I expand the date time for the full time click it
off and then click it back on again, it has now gone back to the default that I
set when I bookmarked it. Again I can’t emphasize enough how important that is
because you may want it to do that you may not want it to do
it just depends on what your settings are but it will preserve the state of
the filter when you bookmark it. OK, so I like how the layout evolved. I like the way
that it works. Now I just need to make sure the layout is correct so I want
those two, I want the toggle buttons to be on top of each other,
and I want the I want the dates to line up with each other like that. When I click between the two of them, that’s what its going to eventually do with the dates and times. OK, let’s go ahead and publish this out and you can see what it looks like once it’s inside the Power BI Service. Let’s go ahead and go and save it, then publish it. Send it out to the Workspace. Once it publishes we’ll go and take a look at
the way the toggle works inside the Power BI Service itself. OK so inside the
Power BI Service you can see that the toggle buttons here, the relative date
filter is active, that’s what’s showing up here. If I go ahead and I switch it
between the other filter you’ll notice it hides the relative and pulls up the
toggle. If I were to change this filter to a different time period, I’ll see
there it’s it’s not pulling up any data because there wasn’t any data for that
time. If I then change it back you’ll notice it it defaulted to the way that I
bookmarked it which is exactly what I was saying, make sure you have it set to
what you want it to be. The same would be true if I went back and said “You know I
don’t want to look at the last four years, I want to look at the last six
years.” I’ll see the data changed. If I toggle it off, and toggle it back on
again, it defaults back to four years OK, so that gives you a quick rundown
of the way that the toggle button can work with the filter using the selection
pane and the bookmark pane together and then tying the bookmark pane to an
individual image and having it switched the way that the screen is filtered and
which visuals are turned on and off. I hope that helped and make sense to you.
Drop some comments, drop a like, let me know if there’s anything else I can
cover in the videos or any other points that you are interested in with Power BI,


Add a Comment

Your email address will not be published. Required fields are marked *