- Yooo! What's up? This is Patrick
from Guy In A Cube. And in this video, we're
going to talk about cascading some slicers,
some do's and some don'ts. Stay tuned. (upbeat music) If you're finding this
for the very first time, be sure to hit that subscribe
button to stay up to date from all the videos from
both Adam and this guy. All right. Cascaded Slicers, there's
lots of great content, lots of videos, lots of blog posts, but I just want to kind of
consolidate all my thoughts on them in this video. So you guys know, I like to do,
instead of all this talking, let's do what? Let's head over to my laptop. Let's say you have a report like this. When you choose an item from one slicer, you'd like it to filter
all the correspondence slicers on the page. Because let's say I select
this NA value right here. It returns blank. And we want to produce a better experience for our report consumers. And this is just not the experience that we want them to have. We always want the combination of slicers to produce, to yield
some types of results. The first thing that I
often see people do is this. They go here, let's find our date table. They'll go and do this. Any attributes that are used for slicers, they'll set the Cross
Filter direction to both. So now when I go here, if I choose 2020, you'll see how all this
change, NA is gone. If I choose France, the list updates. But what's nice about this is, regardless of the combination
of things that I select across those slicers, it should always yield some
results in that in the table at the center of the report. And this works, but there's
some challenges with this and there's a possibility
that it may yield unexpected results. Alberto and Marco and lots of other people have published content about this, and you should just avoid it at all costs. If you can, 'cause there's better ways, there's more elegant ways to solve this. All right. So something else you can use is, you can use visual level filters. And I did a video on visual level filters and it's pretty cool. And they work pretty seamlessly. If the filter that you're going to use, the value that you're going to use as to filter the DAX is not too complex. If you're not going across
multiple fact tables. Let me show you what I'm talking about. So let's say I have this page right here. It's pretty similar to the first one. And so you can see I have
Year and Sales Country and Full Name. And so if I wanted to simulate what I did with the bidirectional, all I need to do is choose
one of these slicers, go to Filters on Visual, find a measure. Let's just choose Total Sales Amount and then say, "is not blank" and apply. And then we will repeat that for each one of the slicers on the page. So I keep doing this and
say, "is not blank", apply. And then also on this
one, Total Sales Amount. So you need to do this
on every single one. And so now I get the exact same behavior. You see how NA is gone. If I choose 2017, and people would change, United
Kingdom, people will change. But regardless of the combination, it always yields a proper result for me. Now the challenge with this
is, like I said before, if you have multiple fact tables, I mean you'll need to bring
in measures from each fact on to each one of those slicers. Or if your DAX is complex it
can really slow things down and this could present a challenge. But the bigger thing is
the DAX that's produced for those slicers, check this out. So if we just go over here, and we go to external tools,
we turn on DAX studio. So we're gonna get DAX studio opening, while we do something else,
we're gonna go to View, and we're going to go
to Performance Analyzer. I'm going to start recording. And we're just going to
refresh one of these slicers 'cause I just want to see the DAX, right? We're gonna copy that DAX,
go ahead and paste it. What you see is that it's
adding to Keep Filters here for the Total Sales Amount. If I added multiple measures, you would see that for
every measure there. And so, like I said, if
the DAX is complicated or if there's multiple fact tables and it requires you to add
multiple measures there, it can slow things down. So you gotta be really
cautious when you use this, OK. The next thing I want to talk about is, what I like to call "The Alberto." Alberto has a great post out there. It's about filtering slicers without using bidirectional
filters in Power BI. And you should definitely
go read this article. But let me walk you through
this, how he did it. And so instead of dragging each one of these measures
or multiple measures because you have multiple fact tables, what you would do is, you
can create a little measure. So I'm going to create
this little measure here. It's called The Alberto equals. And it's a really clean little measure, that he makes, he says,
"if it's not, is empty, "your fact table." Mine is Internet Sales and press enter. This is going to return a one or a zero. If it's empty, it'll return a zero. If it's not empty, it'll return a one. And then what you would do on that page is you would need to add
this particular measure to all of them, but it's a much more efficient query when the DAX is definitely
not complicated. So what I would do here
is I would go right here, to The Alberto, drop it there, say is one, and choose apply. And then I would repeat that
for each one of these select. If I just went to this one, if you wanted to actually see it working, I'll drop that one there,
say is one, and click apply. And you'll see that NA is gone from here. And I repeat that step, all the slicers. This is a very elegant solution. If you haven't read that blog post, you should definitely
go read that blog post. There's a lot of great article. As you know, the Italians,
call it in the article. You should go read that article. And you may be thinking
well, Patrick, the DAX one. What about the visual level filters, where you're using a measure? Well, this one is a little more elegant. Let me show you what I'm talking about. Because if I did have
multiple fact tables, all I need to do is say, "not" is empty. And then the other fact table, right? I don't have another one in this model, but if I had another fact table. And then, it will just pick it up. And so when you do the filtering, it just constantly, inherently
picks up that new fact table as part of this. Where in the other approach
with my visual filters, you were adding measures. And so if you had multiple fact tables, you would need to add multiple
measures to those filters. In this case, you only need
to add one and it just works. It's pretty cool. Another thing that I ran
across was the old Snowflake. So let me show you what I'm talking about with this snowflake. So in the model, this one
right here is a Snowflake. And this one right here is a Snowflake. And Snowflake because of the
direction of the relationships. You'll notice how Product
filters Subcategory, Subcategory filters Product, and Product filters Internet Sales, and Geography filters Customer, and Customer filters Internet Sales. And so, if I go over to the report or I use Product Category, Subcategory, and Product
Name from individual tables and I select accessories,
you'll notice how Subcategory and Product Name, they filter. They're filtered by Product Category. And that's because of the
direction of the relationship. So it's going to filter that one, which would cause this one
to filter that one, right? It just works. Now, if I go to here
and choose Bike Racks, it will filter Product Name, but it does not filter Product Category. And it's the same reason. It's because of the direction
of the relationship. Subcategory can filter
Product and along forward, as long as that arrow, that relationship is going in the right direction, but it will not filter Subcategory because of the direction
of the relationship. I can turn bidirectional on here, but you want to avoid that. Remember I said, at the
beginning of the video. What you do in this case is, denormalize. You would flatten this out,
they're one column dimensions. So I can take the Product
and the Subcategory, flatten it into my Product Category table, either in Power Query or using DAX. My preferences, I would try
to do it in Power Query first, and then do it in DAX. But once it's flattened, let me show you what'll happen is, you kind of have, you just denormalize in your table. And so now if I choose Brakes, it's gonna filter
Category and Product Name. And then if I choose Product Name, it'll filter Category and Subcategory, 'cause they're all just in a flat table. And so, if I go over to my Product table, you'll see that I have
my Subcategory, Category, and Product Name. And they're all hidden
because I created a hierarchy, but they're all in one flat
table and it just works. So that's a lot of stuff. But I want to talk about one more thing, before we wrap this video up
and that's Natural Hierarchies. And so the most common
one is the date dimension. So if you have a Natural Hierarchy, when you're doing the ETL,
if it naturally rolls up, you should do that in the backend. So when you're building
your data warehouse, if you have a data warehouse
in your environment, you should just kind of load it flattened. If it's a Natural Hierarchy. A lot of times you can't, you know, like in the example where I
had Customer and Geography, it's not a Natural Hierarchy, but Product, Subcategory, and Category are date, month, quarter. They typically are. Let me show you what I'm talking about. So you can see in this
Natural Hierarchy right here. If I filter 2021, I know we
haven't finished out 2021. So if I filter it, you'll see how it only shows two quarters. But if I feel to 2020,
it'll show four quarters. If I go over here and choose November, look what happens to the quarter. It's only quarter four, and 2021 is gone because we don't have data for 2021. So regardless of the slicer of where I picked the value from, it always will filter
up and down the levels in that hierarchy. That is because it's
all in the flat table. It's denormalized, similar to what I did
with the Product Category. All right. What do you guys stay? What are you guys doing
with filtering slicers? I tried to make this exhaustive video. I know it's a little longer. But I love to know how
you're solving this, what techniques you're using. Let's continue the conversation where? In the comments below. If it's your first time visiting
the Guy in a Cube channel, hit that subscribe button. You're like my video, big thumbs up. As always from Adam and myself, thanks for watching. We'll see you in the next video.
