Remove filters from single specific columns we'll throw a few of the techniques in along the way to show you the point of doing all this so let's get started.
I've started by creating a new report and i've already imported all the data from the excel movies workbook that we've been using for previous parts of this series.
I'll drop a link in the video description so you can download this file yourself and follow along with the video if you'd like to all i've done since importing the data.
Is tidied up in the data model view a little bit and i've also made sure to hide all the id columns so that they don't clutter up the fields list when we're in the report view.
Our films so i'm going to right click the all measures table and choose to create a new measure and then in the formula bar let's create a new measure called total runtime.
And we'll simply make this equal to the result of the sum function looking for the film runtime minutes field now that i've created a new measure in.
That table i can delete the delete me column and convert this table into a proper measures table and then when i've confirmed that i want to do that i'm going to display the.
Measure we've just created on a simple card so i'm going to add a card visual to the report page and then assign the total runtime field or measure to that card.
Now you probably already know from watching previous parts of this series that the value displayed by a measure can be affected by filters and the filters which affect to measure.
Posts Related:
Can come from a range of different
Sources a fairly obvious place to start is in the filters panel here we can choose to assign a filter.Which would affect either just a single visual all the visuals on the same page or even all the visuals on all the pages in the entire report.
As a quick simple example i'm going to head over to the certificate table in the fields list and drag the certificate field into the filters on this page bucket.
Once i've done that i have a fairly easy obvious way to assign filters to all the visuals on the page just by checking the box next to the certificate i'm interested in and you can see that as i.
Do that it changes the results displayed by the measure i can quickly and easily clear all the filters by clicking the clear filter button there and then i get back to the.
Original result it's also possible to use other visualizations on the same page to filter the result of a measure you may remember again from previous videos that.
Visualizations on the same page interact with each other some visuals in fact are designed purely to act as filters such as the slicer visual.
To draw a slicer i'm just going to click away from my card first of all and then i'm going to draw a slicer on the page and then let's assign the country field from the country table to that slicer.
Apologies the font size is a little small but it's enough to get the idea i think if i now click on a country in the slicer you can see once again it affects the result displayed by that measure and.
Again if i hold down the control key i
Can include multiple values in the same selection and get a different result each time i add a new country again i can then clear that filter by.Clicking the clear filter button and get back to the original result as well as these external filters which can affect the result of a measure you can also generate filters within the.
Visual that the measure is displayed within and to demonstrate that i'm going to add a basic table to the report so i'm going to add a table and then i'm just going to assign my basic total.
Runtime measure to it and just to make it vaguely readable i think i will increase the font size of that using the format pane selected the values section and change the font size.
Up to about 13 and hopefully that will be big enough to read so at the moment where she we're seeing the total runtime of all the films if i wanted to generate a filter context.
Within the same visual all i need to do is add another field to it so in this case i'm going to go to the genre table and i'm going to drag the genre field and position that just before the total.
Runtime measure and you can see that that same measure now is displayed 24 times once for each genre as well as one extra time for the grand total which is the same as the.
Value being displayed up here so in the different genre rows the filter context that's generated automatically influences the results of the same measure.
So what if you wanted to create a measure which isn't affected by filters this is quite a common requirement in dax in power bi and there are several functions which are designed to allow.
You to manipulate the way filters work let's start by adding a new measure to the all measures table and i want to calculate again the total run time but this time without it being.
Affected by filters so let's call it something like total runtime with no filters well i suppose we could do it the way the kids do on instagram come we could do hashtag no filters and we can.
Try to sound young and trendy i suspect it may be a little too late for me to get away with that anyway let's go with them with the calculate function which again you may remember from the previous.
Video allows you to calculate an expression and modified by filters the expression we're going to calculate is the total runtime so we could refer to our existing measure called total.
Runtime or we could nest the sum function inside this calculate function and calculate the runtime minutes here we can then enter a comma and on the next line we can choose how to influence.
The filters we want to remove the filters all the filters from this measure and there are two functions we can use to do that the older of the two functions is called.
All the all function actually serves two different purposes in in dax when you nest it as a filter argument in for example a calculate function as.
We're doing here the job of the all function is simply to remove all the filters applied to that measure so we could just say all open and close from round brackets and then close the.
Extra set of round brackets for the calculate function now the all function also serves a completely different purpose if you use it in a different context.
So to avoid confusion um dax introduced a separate function called remove filters now remove filters only ever appears as a filter argument its only job is to.
Remove filters so it doesn't serve two purposes like all you'll only ever find it in this sort of situation the syntax is the same it's just the function name and then the simplest way.
To apply it is just to remove just to open and close a set of round brackets to remove all the filters in that measure so it's entirely your choice either all or remove filters either will.
Work when you press enter to create your measure you can then display that in several different ways so let's add another card.
And add our total runtime hashtag no filter you see it gives you the same result as the original measure we could also apply this to our our table so we head to the table object and.
Then add the total runtime hashtag no filters this time you will see a slightly different result hopefully you can see that every single.
Genre now is displaying the total runtime for all the films so the filter context that's generated by the genre field is being ignored you'll also see that the.
Um the card ignores any filters we apply externally as well if we select countries in our slicer it affects the original measure but not the new one again as the name suggests we're.
Removing all filters from that measure just to complete the picture again of course if we apply filters using the filters pane it won't affect our new measure but it will still affect the.
Original one that we created now the point of doing this sort of thing is usually so that you can compare a filtered measure with an unfiltered one so for example what if we wanted to.
Work out the contribution of each genre's run time to the grand total now that we've got the ability to calculate the grand's total for each genre context we can simply divide the.