videocam_off
This livestream is currently offline
Check back later when the stream goes live
Up next in 10
Magnificent 7 - Simple Tricks to boost your Power BI Development|| Power Platform Virtual Conference
Nov 6, 2023
Simple tricks are almost always most effective. Impress your users with some cool visualizations, using these 7 neat techniques: from taking buttons to the next level, through creating animated tooltips or using basic DAX calculations to enhance regular Power BI experience, this session will walk you through solutions to different use-case scenarios which can be applied in your day-to-day Power BI development.
About Speaker:
Nikola Ilic
I'm making music from the data! PowerBI and SQLServer addict, MCT, Pluralsight Author, blogger, speaker...Interested in everything related to data - always eager to extract valuable info from raw data in the most effective way. Multi-year experience working with (predominantly) Microsoft Data Platform (SQL Server, SSAS, SSIS, SSRS, and Power BI). Father of 2 and true football (and Barca) fan!
Conference Website: https://www.2020twenty.net/power-platform-virtual-conference/
C# Corner - Community of Software and Data Developers
https://www.c-sharpcorner.com
#conference #powerplatform #powerbi #csharpcorner
Show More Show Less View Video Transcript
0:00
So, hi everybody. Good morning, good afternoon, or good evening, depending on which part of the
0:07
planet you're currently attending this session. And welcome to Power Platform Virtual Conference
0:12
from my side. Today we will have a lot of fun with Power BI. As you may recall from the session
0:18
description and from Simon's introduction, simple tricks are almost always the most effective
0:24
Therefore, I hope that you will enjoy and get some useful ideas to implement in your own Power BI work
0:32
So, by the start, just a brief introduction. My name is Nikol Ilic
0:36
I'm originally from Belgrade, capital city of Serbia. But since the last almost five years, I live in Austria, where I work as a business intelligence developer at company ITSP Services
0:48
I live in a wonderful city of Salzburg, and that was the reason why I've chosen my nickname Data Mozart
0:54
You probably all know that Salzburg is widely famous as a birthplace of
1:00
Volkan Gamadeus Mozart, so I took his last name as part of my nickname
1:05
And that's why my motto is make music from your data. You can find me on the web
1:10
I'm blogging regularly at dataminusmozart.com. I'm also pretty active on LinkedIn and Twitter, so feel free to ping me or connect
1:19
As Simon said, I'm a true football and Barca fan. as you can conclude looking at the photos on your screens
1:27
Prerequisite, well, this is a 200-level session, which means intermediate level. Don't be scared and don't run away if you are new to Power BI
1:34
as I will walk you through step-by-step how everything is created. Of course, some basic knowledge and experience with Power BI
1:41
can come in handy, but I would say by no means you're in trouble
1:45
if you've never used Power BI before. Most of the topics relate to visualization techniques
1:50
so walking through the session with no programming background is completely fine
1:55
As I said, no Power BI experience is needed, but it will obviously help
2:00
And the most important for those of you who are asking themselves, can I sneak through the session without understanding any DEX
2:07
Yes, of course, so don't leave the session because of lack of DEX knowledge
2:12
Honestly, there will be some DEX involved in a few examples, but it will be on a really basic level so everyone can understand what we are doing
2:20
In any case, if someone doesn't understand, feel free to ask a question and I will try to answer
2:26
the best I can. So let me quickly walk you through the agenda. First, we will see how to turn plain
2:32
old matrix visual to a custom-like visual, then how to show our users what they selected within
2:38
the slicers. After that, we will have fun with tooltip pages and as a bonus tip, I will show you
2:44
how to create an animated tooltip page. Then we will see how to establish dynamic filtering within
2:51
your Power BI visuals. Next is building a custom slicer pane for an unforgettable filter experience
2:59
I will show you then different ways to handle blank values in your Power BI reports in a proper
3:04
way. And last but not least, we will learn how to reset all your slicers with one single click
3:10
So let's start our journey. The first thing I want to show you is so simple yet powerful
3:16
that I thought it's well worth including it in this session. First of all, I have to admit that
3:24
Power BI has an extremely large collection of visuals, both built-in and especially custom
3:30
visuals from AppSource Marketplace. One of the built-in visuals that is frequently used is
3:36
plain old Matrix. Those of us who are old enough like me and who used to work with SQL Server
3:43
reporting services in the past, yes, we have fond memories of Matrix as it was a key pillar of all
3:51
our reports. But Matrix as a visual was a long time underrated in Power BI and even well-known
3:59
expand collapse functionality was enabled somewhere in the end of 2019. So I wanted to ask you, do you recognize the illustration on the right
4:09
So this one. For those of you who are not familiar, it's from Google ytics dashboard
4:18
It's quite simple, but at the same time, very powerful because it gives you a quick insight
4:23
in most relevant trends. However, believe it or not, Power BI doesn't offer out of the box visual that would bring
4:31
same or like, let's say similar experience. So I will now show you how you can transform plain
4:38
old Matrix Visual to something like heat map visual, but even more customized. So it should
4:43
mimic the look and feel of this Google ytics visual that I just show you. As I like to say
4:49
when I'm demoing this trick, after we are done, you can freely say that you created your own
4:55
custom visual. So enough talk, it's showtime. Let's head over to Power BI Desktop
5:01
Okay, so here I have you see my metrics which contains data about sales amount
5:08
for all of the brands and for each month within selected year
5:14
which is 2009 in our example. Okay, so there are a lot of numbers but it's
5:19
really hard to spot any trends without going into deeper ysis. So what can
5:25
we do here? I will go to a formatting pane here and under conditional
5:31
formatting I will choose to format my background color property. As soon as I
5:38
toggle on this property you will see that Power BI applied some automatical gradual coloring but I want more control over it so I will click on
5:47
advanced controls. Here I will define my lowest value for my blue color because
5:54
my report is a bluish one. You see that we apply conditional formatting on
6:02
ourselves, on the background of ourselves. Numbers are still there. What should we do as a next step? Apply exactly the same formatting for font
6:13
color. So I will go again to advanced controls and choose exactly the same colors for my colors
6:21
And that's it. Basically, in literally few clicks, we were able to transform our plain old matrix
6:29
visual to a custom-like visual. And this way, for example, you can easily spot that Contoso and
6:35
Fabricum brands, yeah, they are rocking with sales between May and July. And that, for example
6:43
that the worst month of Contoso, which is January, is better than the best month of any other brand except Fabricum
6:51
And the most important thing, you could easily spot those trends in a few seconds without going any deeper into ysis
6:58
Of course, if you need to check some specific figure, you can use tooltips
7:03
Numbers are still there. They're just not visible. So if you hover over here, you can see that sales amount
7:09
for Contoso in May was somewhere above 18 million. So that's quite a
7:16
cool trick for those matrices that are wide with a lot of numbers and
7:22
where you want to enable your users to get a quick insight without going any deeper into ysis Trick number two This came out after I previously presented my session at one of the conferences and one of the questions I received
7:39
during the session was, is there a way to show the actual active filters as a result of the
7:45
slice of choices on the page? While preparing the answer, I realized that, in fact, a lot of people
7:51
were searching for the solution to this challenge. So I decided to explain in
7:55
details today how you can achieve this. Basically, the problem we are trying to
7:59
solve here is when we have multiple slicers in our report and multiple
8:05
selection enabled within those slicers, our users can easily get lost and don't
8:10
know exactly what affects their numbers and charts. Therefore, we need to display
8:15
in the report itself which values have been selected in the slicers. First and
8:20
most important thing here, Dex is your friend. We will learn about iterator
8:25
functions and how can you benefit from using them to solve some
8:31
specific challenges like this one. In the end, I will also share with you a bonus
8:36
tip which will even more improve your user's experience. So stop talking
8:40
Nikola, it's showtime again, let's go to Power BI Desktop. Okay, so I have my slicer
8:47
here. And you see that for example in Brand Slicer I already have multiple
8:52
selections. Here it's Contoso, Litware and Nordwind Traders. So my user doesn't
8:58
have idea until he expands the slicer itself. So let's do something and
9:06
help him to get this information on the report page. So the first step is to
9:14
create a DEX measure that will capture all selected values in the slicer, these three in our example
9:20
You can use two different DEX functions to obtain distinct values from a column
9:25
and these two functions are values and distinct. Values function is more complex
9:31
but I will focus on a more specific scenario. Basically, values will return all distinct
9:37
values from the column we passed as an argument, including blanks. That's important, if blanks
9:43
exist, of course. On the other hand, distinct will return all distinct values but ignoring blanks
9:49
Which one you want to use, in 99% of cases they will return exactly the same results. It depends
9:55
mostly on the business request if your users want to see numbers for blanks or not
10:02
Personally, I prefer to use values function because it gives me the full picture anyway
10:08
So I will go and create a new measure which I will call
10:18
just a few seconds, Power BI selected brands. And as I said I will
10:25
use values function to return brand names from my slicer and that's our
10:32
measure. So let's go and put it on our report page, selected brands
10:43
Oops, that gives me an error. But however strange it sounds, it's okay. I know
10:49
you're thinking now, Nicola, are you crazy? How nerd can error be okay? But just a
10:53
second. For the starter, let's see what the error message specifies. So if we
10:57
click on See Details, I will see a message a table of multiple values was
11:02
supplied where a single value was expected. Okay, so fine. The problem is that values function
11:12
same as distinct function, they brought return table. However, it's not a normal table. It's a
11:18
virtual table created on the fly by DEX engine. So we should apply some additional steps and
11:24
calculations in order to extract single values from it. And here come iterator functions to the
11:30
Iterator functions do what their name says. They iterate over the table and apply the calculation row by row
11:38
How do you recognize iterator functions index? They have letter X in the end, so it's sumX, averageX, countX, etc
11:47
In our scenario, we need to iterate over our distinct values and concatenate them into the string, which will be later used to display in the report
11:57
report. So we will use concatenate x function. So let's go and expand our
12:03
measure, our original measure, concatenate x. First argument is a table. We want to
12:10
iterate over the virtual table which was created using values function. Second
12:17
argument is the expression we want to return. So we want to return a brand name
12:21
which is in the slicer. And finally the third argument is the limiter. I will use
12:26
comma, you can use semicolon, exclamation mark, whatever you want, and yes. So
12:32
basically that will now show me my selected brands from the slicer. Let's
12:39
see if that works if I change something. So if we add AdventureWorks here, and
12:44
remove Litware, you will see that my visual adjusted to show me the current
12:52
selections from our slicer. You can combine multiple different slicers in exactly the same way. So I can also create a similar measure for selected year
13:05
And then I can put also here in the table. Of course, if you don't want to waste a
13:11
space on your report canvas using this table that shows values from from
13:18
slicers. You can use bookmarks to display or hide based on user selection. So let
13:25
me just quickly show you how it's done. First thing, I will insert one image and
13:32
this closing window icon. Oh, it's too big. Let's make it a little bit smaller and
13:40
let's put it here. Never mind. So this is how my page will look like when my
13:44
I selected slicer selection is displayed. Let's go to our view tab
13:52
I will open bookmarks and selection paints, and I will capture the state of the page within the bookmark
13:58
I will add my bookmark and I will turn off data property
14:03
That's important because in that way, our slicers will not be reset when user navigates between the bookmarks
14:11
Let's call this one slicer selection show
14:20
Let's define action for our button to display this visual. Under action, I will turn that on and bookmark
14:33
I will choose slicer selection show. Next thing, we need to define the look of
14:40
the page when this visual is hidden. So let's hide it. This one and this one and
14:47
we will add a new bookmark again turning off data and let's call this one
14:52
slicer selection hide Okay and now if I click on this button I will see my visual Let define action for navigating to a previous one So again going to action property turn
15:08
that on, bookmark and slice the selection hide. Now when I click on this closing
15:14
window icon my visual should disappear from the report page. So let's test if
15:19
that works. Yeah, it works like a charm. So here you are showing it, here you are
15:24
hiding it. And basically, that's an additional level of functionality that provides more space on your report canvas for more visuals and you
15:37
basically preserve space for more visuals using this trick. Next trick, number three, is about tool tip pages
15:52
First of all, I have to say that I really consider Power BI as an awesome tool
15:56
because it gives you flexibility to tell the data story in multiple different ways
16:01
It's really about your creativity. And with little peeking here and there
16:05
you can give your users much better insight into the underlying data
16:10
And they will appreciate that, I assure you. So tool tips are quite useful feature in the Power BI report
16:15
By default, when you hover over a visual, let's say, data bar
16:19
you will see numbers for that specific portion of data, like in this example here on my slide
16:27
But what happens when default is not good enough or when we want to give our users better insights
16:33
into the underlying data with just hovering over data bars? Again, let's go to Power BI Desktop
16:40
It's show time. So I already created two tooltip pages in advance
16:45
two simple pages. This one consists of one card visual showing total sales quantity and top five
16:53
regions by sales amount sorted in descending order. The other one shows me sales amount broken
17:00
down per specific color within one brand. So nothing special. Don't worry, I will show you
17:07
all the necessary steps. How can you build tooltip page from scratch? So let's start. And the first
17:14
step is to click this plus icon here and create a new report page. Okay, I'll turn
17:22
that off now we don't need it for this demo. Okay, and now if we go to formatting
17:27
pane of this page and under page size instead of default 16 by 9, I will
17:33
choose a tooltip type. Next thing I prefer to do is to go to view tab and
17:39
and under page view, I'm selecting actual size. Why am I doing this
17:43
Because it will help me to build my visualizations in proportions to real size of the tooltip page
17:50
Next step is to hide this page. So once your user opens the report
17:57
it will be hidden and they will not see it. And last but not least, as this is the key step
18:03
under page information, you see this tooltip option, and I will turn that on
18:10
This way, we instructed Power BI that we want to use this page as a tooltip
18:16
Okay. So let me check if I did that for my previously created tooltip
18:21
Okay. Here I forgot. Okay. And let's go back to our main report
18:27
And let's say that here, instead of having the default tooltip, which shows me sales amount for specific data point
18:36
I want to use my tooltip page. How can I set this
18:40
Go to formatting pane, all the way down tooltip, and here instead of default, I will choose report page
18:50
and I will choose my tooltip regions. That's my tooltip page. Now when I hover over, let's say February 2009
18:57
I see my tooltip page with numbers for that specific month. If I continue hovering over another month
19:06
you see that data is changing to reflect specific numbers in that month
19:12
Let's do similar thing for brands. And let's say that here instead of default tooltip
19:17
I want to see for AdventureWorks, for example, how it performs for specific color within AdventureWorks brand
19:24
So I will go again to formatting pane, tooltip, and instead of default, I will choose report page and tooltip colors
19:33
And now when I go back and hover over, I can see that white, black, silver was the best performing colors for Adventure Works
19:40
For Contoso, it's similar white, silver, black. So basically, you can check which colors perform
19:47
best within specific brand. Okay, now you're probably asking yourself why did he create two
19:53
different tooltip pages? He could show us everything with one example and that's a great question, so
19:59
let me answer it. Choosing default tooltip page size is not the only option. You can also create
20:05
a tooltip page using custom size entering values you want. So here under page size
20:12
instead of tooltip, you can also choose custom type and then you can manually enter values for
20:18
width and height depending on your needs, whatever you want to display and then use this as a tooltip
20:24
page. Okay, now stick with me, I'll give you one bonus tip and it's really level pro. First of all
20:32
credits go to Jason Cockington from acceleratorbi.com. Jason explained this trick in detail
20:39
but I was so impressed that I wanted to show you quickly here today also. So what is all about
20:46
A lot of our report users are often unaware of some capabilities within Power BI and need some
20:52
kind of guidance in order to use the report in its full capacity. Previously, I was using this
20:57
trick to instruct my users how to use drill through feature within Power BI
21:02
But since we got specific button action for drill through, this trick can be adjusted to assist in some other scenarios
21:11
Let's say that I want to instruct my users how to use
21:15
yze feature of the visual. By default, this feature is not visible to users
21:20
They need to right-click here, for example, in the visual. And then you see this yze option explain the increase or decrease depending on specific
21:30
data point. So it's not so intuitive. It's not easy to find
21:36
So I've created a short animation, a GIF, containing all necessary steps to perform
21:42
yze on the visual. There are a lot of free GIF creators on the web
21:46
I'm using ezgif.com personally. And of course, my GIF was already created before the session
21:52
so I will just walk you through the steps for creating an animated tooltip page using your own GIF
22:00
Essentially, all the steps remain the same as in the previous part of this demo
22:05
The only difference here is that instead of putting GIF as an image on the tooltip page
22:10
I will go here to formatting page and set it as a page background
22:15
So here you see the option to add an image and I will add my GIF
22:22
I did that nothing happened because my transparency is set to 100 But as soon as I move this to zero and set image fit to fill you will see nice little animation instructing my user how to use yze feature
22:37
Let's go back and apply this on our main report. And I will use this visual header tooltip, this question mark
22:45
So now you see by default that my users see total sales amount for a specific month
22:51
and let's say that I want to use this question mark to instruct them how to use yze feature
22:57
I'm going to Formatting pane and all the way down at the bottom
23:01
is Visual Header Tooltip. I will select my report page, page one
23:06
And now when I go back and hover over this question mark, you will see that now my user
23:12
when hovers over this question mark, he can see, aha, I can use yze feature
23:19
and he can see all necessary steps to apply this on the visual
23:26
Before we conclude this topic, let me just go back here and one important remark from my side
23:32
Don't get crazy with creating too many tooltip pages since it will affect overall report performance
23:38
because of longer rendering time. As I often like to say, try to find the right balance between giving your users
23:44
enhanced experience and additional overhead which can be caused by overusing some of those non-standard techniques
23:54
Okay, let's continue our hopefully interesting journey on Power BI tips and tricks
23:59
Next topic is dynamic filtering in Power BI. The challenge we're trying to solve here is that we want to give our users possibility
24:08
to dynamically choose what they want to see in the report. or to be more specific, they should be able to switch between different measures
24:17
on the same visual in most effective way. The first solution here that came to my mind was to play around with buttons and bookmarks
24:25
But then I thought, what if in the future my users wish to expand on this
24:30
and include some additional measures? I mean, it's easy when you need to provide selection between two or three different options
24:37
But things quickly become very complex if you choose to use bookmarks and need to increase the number of possible outcomes
24:46
Therefore, even if I love bookmarks, I discarded them as a solution here, since I thought it would create a real headache when it comes to maintenance
24:54
I will now show you a much more flexible solution, which can be created pretty quick
25:00
And what is most important, can be quickly upgraded to support additional similar requests
25:06
So essentially, we are getting flexibility and scalability in one solution. Who doesn't want that
25:12
So enough talking. It's showtime. Let's go to Power BI Desktop. I've already prepared some basic stuff, as you can see here
25:21
And I've also created a few simple measures that we will need for this demo
25:26
And here I'm using explicit measures. This one's sales amount is just a simple sum over sales amount column
25:33
I know that you like things that are automatically created for us
25:38
and Power BI does pretty well in that regard. But without going any deeper, I would just say that you should avoid using implicit measures
25:45
whenever possible and create explicit measures instead. So this is the first one, sales amount
25:51
The other one is sales amount year to date, which uses just the calculate function
25:57
over this sales amount measure, which I showed you previously, and built-in time intelligence function that's year-to-date
26:06
Basically, this measure will return running totals for selected years. So it will pre-aggregate all previous values and showing running totals
26:21
So nothing special, just wanted to show you the difference between those two
26:26
So now the idea is that we want to enable our users to select what they want to see in this visual, in one visual
26:36
either sales amount broken down on month level, like you see by default, or running total, so year to date
26:44
Okay, here the party starts. So first thing we need to do is to create a new table, which will hold data for our dynamic filter
26:55
and I will go here to enter data and let's call this table calculation time frame
27:05
I'll need two columns ID and let's call this one period. This one too and period is monthly and year to date
27:18
I will load this to my data model. As you can notice, you can easily extend your options in the future if needed. Just add another
27:26
ID and new measure here. So now we need to know which measure user selected so we know what to
27:36
display in in our visual. So let's create the measure that will capture user selection. I will
27:41
go here and create a new measure, which I will call SelectedTimeFrame
27:48
It equals min, so we are using min function to capture the minimum ID from
27:55
our calculation timeframe table. This measure will return minimum ID value of
28:02
the user selection. In case that no value is selected, the measure will return ID 1
28:07
which is the minimum ID value by default. And that is monthly way of displaying data in our case
28:14
Now stick with me, here is where the magic happens. So far we have captured user selection, but it's still in some way disconnected
28:22
from our existing data model. Let's see how can we put user selection into the context of our whole data model
28:30
So I'm going back to my fact table and I will create a new measure here
28:37
Let's call it SalesAmountSelected and now I will use the Switch function to return
28:45
selected time frame and I will say if it's number one, return me sales amount
28:55
normal sales amount. If it's number two, return me sales amount year to date. For
29:02
the sake of proper naming I will remove those table prefixes from our measure
29:08
names and let's confirm this. Let me stop here for a second and explain what is so
29:14
special about this measure. This measure takes ID value from user selection and
29:19
based on that value it will display respective calculation with help of
29:23
switch function. In most simple words, if user chooses ID 1 which is monthly
29:29
return me sales amount value. If he chooses number two which is year-to-date
29:33
return me sales amount year-to-date. The final touch here is to create a
29:38
slicer and put a period value here. Let's format it a little bit, make it
29:46
horizontal, and move it here. I will drag my sales amount selected measure
29:53
within the visual. As you see by default, we see sales amount broken down by
29:59
monthly level. But if I click here on year to date, you will see that visual adjusted and showing me running totals
30:05
Again, clicking on monthly will show me sales amount broken on month level
30:12
And here I'm seeing running totals. Now, this was just a basic example of using this technique
30:17
but you can virtually implement it in multiple different scenarios, and it will work like a charm
30:24
This pattern gives an amazing amount of flexibility to use the same visual over and over
30:29
and look at different metrics within the same visual. In the end, this removes the need for bookmarks
30:35
and more visuals for each single calculation. Okay, so far so good, I hope
30:43
Next thing I would like to talk about is using bookmarks to fulfill some specific business requests
30:49
For those of you who are not familiar, bookmarks are one of the favorite and most used features in Power BI
30:55
Basically, what a bookmark does, It captures the current state of the report, so you can use this captured state as a reference in your actions through buttons, images, shapes, whatever
31:08
This will essentially give you a possibility to create an app-like experience for your report and make it look more interactive
31:17
There are really dozen use cases of bookmarks, but I will focus on a specific challenge in this demo
31:24
I would say it's a frequent problem, and I assure you that sooner rather than later
31:29
you will face this problem during the report designing process. Okay, so we created our nice little Power BI report
31:38
Everything looks fine. We have our three, four slicers on the page
31:41
So far, so good. But one day, user walks into your office and asks to have additional slicer for, let's say
31:50
brand category. No problem, I will make existing slicers smaller and I will provide space for the new one
31:57
Then after a month or two, user again comes to your office and asks to have one more slicer
32:02
Then after a month or two, one more, etc. I mean, I think you get the point
32:08
So what should we do when we can't accommodate all requested slicers on our report page
32:14
Well, first option, the most obvious one, is to tell our users to choose which slicers are the most important for them
32:21
and then simply discard others. Just kidding. Of course, you shouldn't do that
32:27
We can create a separate report page which will contain slicers only
32:32
or we can create a slicer pane which will hold all of our slicers on the existing report page
32:40
and we will be able to show or hide this slicer pane depending on user's choice. If you don't trust me, let's go to Power BI Desktop
32:48
and I will show you how clever usage of bookmarks and actions can make you a
32:53
true Power BI hero. So here I have report page with multiple slices. Not so far
33:02
but let's multiply this one just to show you the magnitude of our challenge. So
33:11
Let's say that our users need all of those slicers here. First thing you need to do when you're working with bookmarks is to
33:21
turn on bookmarks and selection panes. Now one more thing I need to do to
33:29
put proper order for my visuals. That's it. and here I will also use my icon for closing window Again let make it a little bit smaller and it
33:49
fine to be here. I will use one more icon. I will explain you why. This is a
33:56
menu icon. Let's put it here. It's fine. Okay, so this is how my report will look
34:05
like when the slicer pane is expanded. So let's go to bookmarks and capture the
34:12
current state of our page. I will turn off data properties so that my slicer
34:17
selection remains the same and I will call this one slicer pane
34:23
expanded. Here I will define action so when my user clicks on this menu icon I
34:31
I will show him this slicer pane. So go to action, turn that on, and I will choose my slicer pane expanded
34:39
Now let's define the look of our report page when slicer pane is not visible, so when it's hidden
34:47
Let's go and hide all of those slicers, including this shape, and this is the
34:55
state of the page that I want to refer when my slicer pane is hidden
35:01
So let's go and capture this, turn off data. Let's call this slicer pane collapsed
35:13
Okay, now when I click on this menu icon, I hopefully will see my slicer pane
35:19
Yes, it is. It's there. And now let's define action for closing window and go to action, bookmark
35:28
and I will choose to slicer pane collapsed. Now if we click on this closing window icon, it will hide my whole slicer pane
35:37
Here I'm showing it. Here I'm hiding it. So as you saw, we pushed filtering experience to a whole new level
35:44
just by using bookmarks in our report. Your users will love you
35:48
You supplied all slicers they wanted, but you also saved space on your report
35:53
to accommodate more visuals or to give existing ones a little more space if necessary
36:01
Okay, number six. We are trying to find a solution to one of the most common challenges in Power BI reports
36:07
Now, the problem we are trying to solve, or it's not a problem per se
36:13
but more, I would say, enhancement to our users' experience. Well, let's say that we have created a few measures in our report that for some of the visuals will produce blank results
36:24
I would say that it's a fairly common situation to have blanks in the visual as a result
36:30
And don't get me wrong, there is nothing wrong with that, nothing wrong with having blanks in the report
36:36
But sometimes you just want to substitute those empty cells with more intuitive values
36:42
such as zeros if you're working with numbers or not applicable if you're dealing with text values
36:48
There are multiple different ways to handle this request. Each of them will produce exactly the same outcome
36:53
So I just wanted to share with you three possible solutions to a challenge called
36:58
replace blank value with a zero or not applicable. So it's show time
37:03
Let's go to Power BI Desktop. Okay. So here you see that I have sales amount for every specific brand and let's say
37:15
that I want to include total sales amount for only one single brand here I create a new measure Let say that I want to see only sales amount for Contoso so it sales amount Contoso and I will use calculate function Sales
37:34
amount. I already have this measure and here I'll just keep filters on
37:45
Brand name equals Contoso. Okay, so nothing special
37:56
Now if we put this sales amount Contoso measure here, you will see that I have total sales
38:03
amount only for Contoso. I have blanks for all other brands. And let's say that the intention is to substitute all blank values with zeros here
38:13
So let's go to a first solution. And this is probably the most obvious choice
38:19
And I suppose that most of us use this formula when dealing with blanks
38:24
So how it's done? I will call it blank version one. And it equals, so we are using if function
38:33
And we are checking if is blank. if the value is blank
38:41
for the measure sales amount contoso. It will return true or false
38:50
So if it returns true, I want substituted as true. I will provide zero value
38:58
And if not, I want to return sales amount. Let's drag this measure
39:06
to our visual, and you can see that we now have zeros instead of blanks
39:13
Second option. Yeah, this was a pretty straightforward solution, but let's check the other option, second option
39:19
which was introduced not so long ago. I think beginning of last year when we got a brand new DEX function called Coalesc
39:26
For those coming from SQL world, this is a well-known function, but let me explain briefly for those of you who are not familiar with it
39:35
Basically, Coalesc will walk through the values passed as arguments and return first non-blank value
39:42
In SQL, it's not null here. So let's call it blank version 2 and Coalesc
39:52
So I have column number 1, column number 2, blah, blah, blah, column N
39:58
So it will go and check. If column number 1 is blank, I will go and check column 2
40:02
If it's also blank, I will go further, further, further. If all the values are blank, I will provide in the end, default value that I want to return
40:10
So in our case, it's just checking if sales amount Contoso is blank
40:17
If it is blank, I want to return zero. So just simple as that
40:22
And let me drag this measure here. So exactly the same outcome
40:28
We got all zeros except for Contoso, of course. finally the third one was a kind of enlightenment for me i saw this trick from jeffrey wang who is
40:38
one of the dex creators so let me show you what's the trick uh in that index blank plus zero is zero
40:44
i know yes for us coming from sql environment i was thinking of blank as null twin from sql
40:52
and it's completely unexpected behavior because if you run something like that in in sql server
40:59
Management Studio select null plus zero and execute this that null so it not zero But in Power BI null plus zero is blank plus zero is zero So let me show you this last solution
41:17
And it's blank version three, and it equals, so we are just saying online sales amount Contoso plus zero
41:29
and that's it. Simple as that. I'll drag this measure to a visual and you see that we are getting again exactly the same results
41:40
As you witnessed, we've learned three possible ways to deal with blank values in our Power BI reports
41:45
Which one you want to use depends on your personal preference. If you ask me
41:50
Coalesc offers much more elegant syntax than IF and I prefer to use that one
41:56
Last one. Here we are again back to slicers and bookmarks which work perfectly in synergy. Now
42:06
again, the problem we are trying to solve, I would say it's not a problem. Again, it's more
42:11
again, improvement of users' experience. Well, let's say that we have many slicers in our report
42:19
which is a pretty common situation in real life. And our users have chosen different values in each
42:26
of those slicers to filter the data and now they want to go back where they
42:30
started and clean slicer selection. Option number one is to go and clear
42:36
selections in slicers one by one but that's quite cumbersome and tedious
42:40
especially if you have many slicers. Therefore I will show you now a very
42:45
simple trick how to reset all the slicers with only one single click. So no
42:50
matter how many of slicers you have in your report, three, five or ten, one single
42:55
click will be enough to revert them to a starting point. So it's
43:00
showtime again. Let's go to Power BI Desktop. The first step is to insert a
43:06
blank button and let's put some self-explanatory text in it like reset all
43:20
Okay, now you see that my slicers are all resetted. Let's go to bookmarks and
43:28
capture the current state of this report page. Previously we were turning off
43:34
data option. Now we don't want to do this because we want to transfer the
43:40
status of our slicers when going back to this bookmark. So I will
43:45
call it reset all and we need to define action for this one
43:55
Reset all. Okay let's go and now choose a few values here in brand slicer
44:02
Contoso, Northwind Traders and Prosware. If I click on reset all it will reset my slicer
44:07
If I choose let's say here 2008-2009 here I will choose for example March and
44:14
July and September, and here I will choose Contoso and Southridge Video and
44:21
Tailspin Toys. Now just imagine that your user want to start fresh again. He will
44:26
need to go in each of those slices one by one and clean selection. Now just one
44:31
single click here, voila, and everything is back to where it was started
44:38
Yeah, so basically that was it
#Online Communities