Enhancing Data Segmentation with Slicers in Power BI

Slicers are a way to slice and dice your data so that you don't see all of it; you see a certain segment that you're interested in. So, for example, here, we're still working in the Adventure Works report that we created in a previous video, so go ahead and open that up.

I want to see this information filtered by something about our customers. I want to only see a segment of our profit, our orders, our customers, for let's say homeowners. So I want to slice, and there are a couple slicers that we have.

We have the slicer and the button slicer. These are aesthetically a little different; there are different formatting options. I'm going to be using the regular slicer.

I'm going to click on slicer there, and it looks like a little funnel there because you are filtering your data, you're funneling down to only see part of it. So right now we see all of the rows, all of the data that we're working with, and let's say, into this slicer, I insert homeowner. Now with homeowner we have n for non-homeowner and y for yes is a homeowner.

And notice when I do this slicer here, our amounts go down. We're seeing less because essentially, I'm saying I only want to see data for homeowners or people who don't own a home. So we're seeing a slice of our data, a portion of our data.

Behind the scenes, what filters or slicers are really doing is they are only showing you data for certain rows. So if I have no slicer and no filters, then that means I'm seeing all of the data. I'm doing these calculations with all of the rows in our data set.

But when I apply a slicer, it's only showing me those rows. So essentially, it kind of filters out the other rows, and I'm only seeing those totals for the rows that I'm interested in. The people who are homeowners, or the people who aren't homeowners.

I can either click on this to toggle it off, or if I have some sort of selection, I can hover over and click this clear button. Looks like a little eraser, and I can clear that slicer. And you can have multiple slicers.

You can stack slicers so you can get even smaller and smaller portions of your data to really hone in on exactly what you're interested in. So with that slicer deselected, I just click into an empty area there and be careful with double clicking. Double clickers hate this.

It keeps creating this Q&A visual, so if you ever accidentally double-click and you get that, just remove that particular visual there. So be careful with double-clicking. All right, I'm going to insert another slicer here with nothing selected so it creates a new visual, and in this, I'm going to insert marital status.

So we have M and S. So M is for married, S is for single, and you can stack these. So I could say I want to see people who are single yet own a home. And so now I'm seeing how many customers are single but are homeowners.

I could go even further. I could use my other visuals to say I only want to see people who are bachelors who own a home who are single. Now I'm down to those.

How many orders did they place? So all of these things are cumulative, so you get smaller and smaller sets of data that you're working with. So you really filter down or funnel down into exactly what you're looking for. Now I'm not a big fan of how this looks in terms of the labels.

The N, the Y, the M, the S. I think as a user of this, that's not very attractive for me to see. I'd rather see something like married, single, homeowner, non-homeowner, or yes, or no, something like that. From a database perspective, I can understand why we were storing just individual letters because that takes up less storage in the database.

And these data sets were exported from a database, so the CSV files that we imported just have those same letters that are stored in the database. But for my reports, I want something nicer looking. So there are a few ways that we can change this, and I want to talk about those different ways and the performance impact, the ease of doing so.

There can sometimes be multiple ways to do the same kind of thing in Power BI. And there can be a difference in how easy it is for you as a developer of these reports, and also, it can affect the performance of your report, in terms of how fast the report works. With our small data sets, here we don't really notice much of a performance difference.

But if you're working with a lot more data, so you're starting to work with millions of rows, those types of reports where you're dealing with larger data sets, there can be a difference in performance that can impact how you're using the report and how quickly it responds to things. So it's worth thinking about the performance when you're dealing with larger data sets. We have two different slicers, and I'm going to show you two different ways of changing the values shown in those slicers.

The first way we're going to see is using DAX. Now, as a reminder, when you're typing in your DAX code or your DAX formulas, you can use Ctrl plus and minus to make the text bigger or smaller. Also, be careful of hitting enter.

Enter applies the formula. It does not go to the next line. So if you want to go to the next line, do shift enter to go to the next line.

And be wary of hitting enter accidentally, maybe before you're finished doing your code, because if you've opened up a parenthesis, when you hit enter, it will probably close the parenthesis. So just be wary of it adding extra things that maybe you didn't type in for you, and just only hit enter when you're all done. Otherwise, use shift-enter to go to the next line.

Now, what we're going to do for this first example is we're going to create a new calculated column using an if function. Essentially, we're going to say if the value is y, then say yes. Otherwise, say no for the homeowner.

So, what the if function does in DAX is a very simple function compared to sometimes what you can do with if in other coding languages. In DAX, all if does is say let's test a condition. Let's test if something's true, and if it is we're going to do something if it's true.

Otherwise, it might not have to do anything at all. Just say if this is true, then do something. There's an optional thing that we can add that says if it's false, do something.

And that's all if can do. There's not an else if to do additional things. While you can kind of nest ifs inside of other ifs, because if you test something to see if it's true, and then do something if it's true, the else part of it would be, well, what if it's false? You could nest a whole other if statement in the false part of it.

But that kind of gets messy with ifs inside of ifs within ifs, and you can infinitely nest these things, which gets kind of complex. You can also use a switch function instead, which would probably make it simpler. And you can click this link if you want to learn more about the switch function.

These links go out to the learn.microsoft.com, which is their DAX reference. So if you want to learn anything about DAX straight from the source, Microsoft, which created this, you can check out the Microsoft Learn website. So let's see how this works here in our AdventureWorks report.

I'm going to go to the table view over here, and this information here is in our customers' table. All the way on the right here is this homeowner column. So this is the column of data that we are using for this slicer here.

All right, so I'm in table view. I have the customers table selected on the right. I have scrolled all the way over here to the right to see this homeowner column, and I want to create a new column that contains the values that I want.

So instead of n for no, does not own a home, and y for yes, does own a home, I can say whatever I want in this new column. I could say yes and no. I could say homeowner, non-homeowner, owns a home, or rents.

You know, if that's what the other option is, you know, just says they're not a homeowner, doesn't mean that they rent, but the point is, you can say whatever you want. It doesn't matter what your original values are. You can choose the new values, and they could be anything that you like.

We're going to say yes and no just to write it out instead of y and n, and so this is a new column that's going to have new values. So I'm in the customer's table. I am creating a new column.

I'm going to hit control plus here just to make this bigger so you can see better. This new column name, I need to call it something that makes sense. Let's say homeowner, with a space, because the current column does not have a space, so I'm going to add a space.

Okay, so that's going to be the new column name, and what I need to do is say if, because I'm going to look in this other column and say if parentheses, what am I going to test? And then, what am I going to do if that's true? Comma, and then what am I going to do if it's false? Notice the false has square brackets. Square brackets imply optional, so we have to do these two, but we do not have to do this. That is an optional thing, and the result, if it's false, that's where you could nest another if if you wanted to.

No pun intended there, but of course, you can infinitely nest ifs, and that gets kind of complex, but it's possible you could do that. So we're just going to simply test to say, is this homeowner either n or y? It doesn't really matter which test we put in here, but I'm going to say, okay, let's go to the homeowner. So I start typing in home, and notice it finds this column right here in this table, so I'm going to hit tab to accept that code hint, and that's my column, and if you want to put spaces around, you can.

You don't have to put spaces. That's up to you. Comma.

Now, what are we going to do if that's, oh, actually, I forgot to finish my test. I'm looking in that column, but what am I looking for? I want to say, is that equal to, and then this is text. Text we put in double quotes for strings.

So I'm saying, if the homeowner equals Y. So that's the text that I'm looking for. So if I see any Y's in there, let me scroll around. There we go.

There we have some Y's. If that's Y, what do I want to say in this new column? This is where I can say yes, if I want to, or I can say homeowner. I can say whatever I want, and for right now, I'm just going to close that parenthesis so we can see that this part works, and I'm going to hit enter.

Give it a moment, and now we have a new column. We're put back up here to the top. I'm going to scroll down until we find some Y's, and here we go.

Now, I did not say what to do if it's false. I just said, if it says y in that column, then say yes in this new column. I can, of course, optionally put the false part, which is the third part.

I'm going to put a comma. Now, I'm doing the what if it's not y. Well, then it's obviously no because we only have these two values in this case, so then I can say put in a string of no. I'm using these quotes because they are strings or pieces of text, and I hit enter to apply them, and now we say yes or no depending on those values that are there, and there we can see both of those.

Again, if you want to change yes to homeowner, if you want to change no to non-homeowner, or does not own a home, whatever you want to say, that's up to you. You're choosing what you want to do. Now, we do have two columns, and as a builder of this report here, I see two columns, and that's kind of confusing.

I don't know which one I should be using, so over here in this current slicer, I'm going to make sure that I clear the selection, and I don't want to be using this one. I want to uncheck this homeowner, and I want to use this homeowner, but I don't even want people to see this one, so I want to get rid of that in this report view because I don't want people building reports with this, so I can go here to homeowner and go into the menu, click on that dot dot dot, and I don't want to delete it from the model because I need that new column to look at this one to determine what's going to go in that new column, so I can't get rid of it, but I can hide it. I can just not show it here, so I can hide it.

Now, I can hide it here, or I can also be in the table view, and I can go here, and I could right-click on the column, and I could hide in report view. Here, they do say hide in report view. In the other report view, it just said hide.

It's the same thing. I can also go over here, go into the menu, and say hide in report view so that I won't see it. It's important that it's hiding it only in report view, so when I hide it, I still see it here because if I'm building DACs, columns, etc., this is still part of my dataset.

It gets imported into Power BI. It gets used here in Power BI to create this new column, so I need to see it here to know how this column is generated because I need to be able to look at this one, but I just don't see it in report view, so people will not build their reports using that column that I don't want. Now I get a nicer slicer with values that I want, and people won't be confused.

They will just see this new proper column, so now it's easier for people to build reports with nicer values. That is one way that we can do that. Let's see a different approach that uses a replace values feature found in Power Query.

If we remember the ETL process, extract, transform, which uses Power Query, and load, there's going to be a performance difference between our DAC's calculated columns, like we just saw, and this new technique of using replace values in Power Query. So what happens is there's this engine called VertiPack, which is how we store data in Power BI. You don't really see that in Power BI, but it works behind the scenes.

So what happens is when you import your data, you extract that data, and you transform it, and then it loads. That loading process is where VertiPack compresses and optimizes the data to create the best performance in Power BI. What happens is that after that, DAX comes in.

DAX does calculations, such as calculated columns. That happens after Power Query, after the loading process. So when we think about more upstream versus downstream, upstream is the original data files that get extracted.

Then we're moving downstream to transforming, loading, and finally with DAX. So if we go more upstream, then it's going to have better performance. If we go further downstream, it's going to require more memory, processing, and calculations.

So DAX is going to require more memory and calculations, kind of processing power to do. So if we have a choice of going more upstream and doing it in Power Query before VertiPack compresses it, those transforms that we do, they can be done once. They don't have to be continually done, like calculated columns need to be continually calculated.

So when we think about performance, it's going to be better to do it in Power Query. So in this case, this is actually going to not only be easier for us to do, but it's also going to be better for performance. It will require fewer memory requirements.

It will take less processing power. So it's actually going to perform better in Power BI. For this technique, we're going to change the values M and S here in marital status.

And we're going to do this using transform data back in Power Query. So I'm going to click the transform data button. This is in our customers' table.

And we find the marital status column here. And all we have to do is just right-click and replace values. It takes that M and puts it here.

You type in what you want it to be instead, and you say married, for example, and hit OK. That's all you have to do. All the M's become married.

Here, if I find S, I can right-click on that and say replace values. It again puts the S. I type in single. And actually, I'm going to make a typo specifically so I can come back later and make a change to this.

I'm going to hit OK. Now all the S's become single. Now, notice that these are applied as steps under applied steps.

If I go back to change type, which is before I replaced either of those values, I can see the original values. If I click on replace value here, I see that. And the last step here.

Now, if you're sharing this with other people, or if you will be making updates, these generic names are not very helpful. Because if I ever want to change the value, what I don't want to do is right-click on this, replace again, and add another step, like change it to the wrong thing, then change it to the right thing. No.

Instead, what I want to do is actually edit the step that made it wrong to begin with. I want to go back and change the settings for this step. So there's a gear over here.

And if I click on that gear, I can see the settings for that step. And actually, I can fix it. So instead of having two steps, I just keep that one step, right? I don't want to change it to the wrong thing, and then change the wrong thing to the right thing, because that's two steps to end up doing that.

Therefore, the names of these steps, you can edit those. If I right-click on this, I can rename it. So, for example, this one changes S to single.

This one here, right click on that one, rename. This is change M to married. So in the future, this is much clearer to other people or your future self.

Be kind to your future self. When you come back to files, and you're looking at your steps, and you're wondering, what do those steps do? Just being able to see a nice name makes it so much easier. It's not required to make it work.

It's just much easier when you come back to make changes. So now, if you want to change that M to something else, you'd go to the settings for this, and you could change that one. Or if you want to change the S for single, you could change that one by clicking the gear over there.

So that's how we change the settings for a particular step. So we don't have to just exit out and then redo it. We can just change the settings.

When I'm all done, I'll come back here to the last step, and I see my final results. That looks good. And I'll hit close and apply.

And here's a cool thing. Watch that update, because it's going to go, and it's going to refresh all those visuals once it's done doing this. So it reloads the data.

It recompresses it with the VertiPaq engine. And then it uses that already compressed and optimized data in our visuals. So, unlike before, when we had the homeowner column, and we needed to swap out the old column for the new column, one of the advantages of this is that it actually updates all the existing visuals that use that.

But even better for performance, because that was done in the transform step in that ETL process. It transforms it, does the change, loads it, and that loading process, that VertiPaq engine optimizing and compressing the data, that was a once-and-done thing. Every time I hit refresh, if there is new data, it'll go reapply that transform step once.

And then it's compressed, it's optimized, ready to create our visuals as quickly as possible. And the performance of that being more upstream is going to be better than doing it more downstream in DAX. Now that's not to say that we shouldn't use DAX, but if there are two different ways like this, I think hopefully you see that this is obviously not only easier, but also better for performance here.

I know in a small data set like this, you're not going to notice any difference. But as you get to bigger and bigger data sets, as your number of rows grows, the number of columns, the amount of data that you're dealing with grows, that is where performance can become noticeable, in your larger data sets. So I just wanted to explain some different ways to create things.

And it's not always just about the easiest way for you to do things. You do want to understand the performance implications of which way you'll do it.