We'll also explain some of the limitations of calculate filters and how to use the filter function to circumvent those and then at the end of the video we'll provide a quick introduction to the calculate table function so plenty to do in this one let's get started to get started i've created a new report and i've already imported all the data from an excel workbook i'll drop a link in the video description so you can download this movies file yourself and once you've imported all the tables contained within that file you'll end up with this list of tables shown here.
If you want to see how those tables are related take a quick look in the model view and this is a fairly similar setup to previous videos in the series i've added a blank all measures table with a column that we will delete shortly the only real major change i've made here is to hide all of the id columns from the report view so that they don't clutter up the field list when we're creating our visuals later to get started i'd like to create a simple table visual that shows a list of the film genres and then create a measure that shows the average run time.
In minutes so i'm going to start by adding the measure first i'm going to right click the all measures table and choose new measure and then when the formula bar appears we can create a basic measure called avg run time and we'll simply make this equal to the result of the average function looking for the runtime minutes field so nothing complicated about that once we've created that measure that means we can then delete the delete me column so i'll convert that table into a proper measures table and when we've confirmed that we want to.
Do that we can finally add our table visual so we'll add a basic table visual to the report page just hide the filters panel for now we'll add to the genre table and add the genre field to that table and then add our average runtime measure to it just to help read this on screen i'm just going to head to the format panel and then just change the uh the font size in the values sections let's bump that up to about 13. that should make it a little bit more readable.
Next i'd like to create a new measure to find the average runtime of films which haven't won any oscars and to do that we'll need to apply a filter to effect which values from the runtime minutes column get included in the average function this is a perfect example of something that the calculate function can help us with so to get started let's add a new measure to the all measures table and i'm going to call this one something like avg runtime losers or average runtime non-winners if you prefer something less insulting and then we'll use the calculate function to.
As this is a fairly simple calculation to create it's no great effort to write the entire expression into the calculate function but if you've created a slightly more complex measure and you wanted to apply filters to that then you can reference the existing measure so rather than using the average function we can refer to our avg runtime measure in this example we'll get exactly the same results either way then we can type in a comma and then on the next line let's add our first filter so i'm going to refer to the oscar wins.
Column of the film table and i'm going to check if the value of that column is equal to zero this is my simple filter basic boolean expression if this condition is true that row will be included in the average runtime calculation that we've performed in our previous measure so if we enter that measure and then add that to our basic table we'll hopefully see a different result while we're here let's calculate the average runtime for winners as well so i'm just going to quickly copy this.
And create a new measure and then paste all the code that i've just copied into the new measure change its name so it's average runtime winners and then check where the oscar wins is greater than or equal to one we can then update our measure and add this one to the table as well and there we go we've got a comparison of the average run times for winners and losers next i'd like to create a new measure that calculates the average budget for films between one and 2 hours long and.
To do that we'll need to add multiple filters in the same measure let's start by adding the measure to the table i'm going to call this one avg budget 1 to 2 hours or hrs and then we'll use the calculate function to calculate the average budget dollars so we don't already have a measure for this of course so we'll need to nest the average function inside the calculate function we can then add our first filter as usual so after a comma we can then say something like runtime minutes greater.
Than or equal to 60. to add the second filter we've got a range of different choices here perhaps the simplest is just to type in a comma after your first filter to expand the parameter list of the calculate function you can see as you add more commas and more filters the parameter list expands to allow you to add even more filters so for filter number two i'm just going to refer to the runtime minutes field again and then check if that is less than 120 or less than or equal to 119 if you.
Prefer so when you add a comma separated list of filters the filters are joined
Together using the equivalent of the and operator so this condition must be true and this condition must be true in order to be included in this expression so let's just enter that to create that measure and then while we're here we'll create a copy of this to get the average budget of films between two and three hours just to show you one alternative way of joining conditions together using the and operator.So i'll copy that code and then add a new measure we'll paste it all in change the name to two to three hours and then alter the numbers from 60 to 120 and then 120 to 180 this time rather than using a comma to provide two separate filters i want to combine the two conditions together into a single filter argument so i'm going to remove that comma between my two filters and then replace that comma with a double ampersand symbol this is one way to apply the and operator in dax.
You may remember this from the video on the if function that we covered earlier in the series so having done that i can enter that new measure and then we can drop both of these measures into our table so that we can compare the average budgets of films with a relatively short runtime versus a relatively long runtime you can also combine filters using the or operator for the next measure i want to calculate the average runtime for.
Films which belong to the fantasy or the science fiction genre so to do that let's add a new measure and i'm going to call this one avg runtime fantasy sci-fi and then we'll use of course the calculate function to get this result as we already have a measure that calculates the average runtime let's make use of it let's refer to the average runtime measure and then we'll add a comma and head on to the filter1 parameter so we'll first of all refer to the genre.
Genre field genre table genre field and ask if that is equal to the word fantasy now we can't write a comma separated list of filters this time if we move on to the filter2 parameter that will be combined with the first filter using an and operator and there is no film which belongs to both the sci-fi and the fantasy genre in this database each film belongs to only a single genre so i'm going to take away the comma there and replace that with the double vertical bar of the pipes which is the equivalent of the ore operator again you.
May remember that from the video on the if function then i can refer to the genre genre field again and check if that is equal to science fiction and then having done that i can close around brackets and hit enter to create that new measure now it seems a little silly to display my table grouped by genres so let's get rid of the genre field from there let's group the table by a different category this time let's go for the country field and then we can add the average runtime.
For fantasy and science fiction films and see the result per country combining filters like this using the or operator isn't too difficult but it can make your measures quite long-winded when you've got lots of different values to test for so for example if i wanted to add in a third genre let's go for martial arts i'd need to place the or operator in again and then refer to the genre field again and then change the value i'm testing for so let's say martial arts this time.
Now the technique works of course i'll get the results i was after but maintaining that measure is a bit awkward it's not particularly nice to read when you're comparing the same column against a list of different values with all operators there's a much simpler solution to this we can exchange the first equals operator for the in operator instead we can then remove the references to the genre column in front of each of the other values so we only reference that genre column once we can then replace the vertical bars.
With commas to separate our list of values so let's say fantasy comma science fiction common martial arts and then finally we need to wrap up our list of values in a set of curly brackets or braces so i'm just going to take these down to the next line so they each occupy their own separate line and then close the braces before we close around brackets so it won't affect the results it just makes the measure a little easier to write and to maintain.
Dax also allows you to use a variety of functions to set your filter arguments and as one quick example of that i'd
Like to create a couple of filters involving dates now we'll talk about dates in a lot more detail later in the series there's an awful lot you can do with them for now this is just a quick basic example i want to show of the average runtime of films released in the year 2000 so let's add our new measure and i'm going to call this one avg runtime 2000 and make this equal to the result of the calculate function and as we already.Have our measure that calculates the average runtime let's make use of it again for the first filter i want to refer to the release date field in the film table i'll just hit the escape key to clear that tooltip that's getting in the way then i want to ask where the release date is greater than or equal to the 1st of january 2000. to do that i can use the date function to construct a date by passing in the number of the year and the month and the day so 2000 for the year followed by a comma one for the month and one for the day i.
Can then close the round brackets and there's my first filter created i can then add a second filter i'm just going to quickly copy and paste the first one add a comma and then paste in what i've just copied and then i can check where that is less than or equal to and then change the number of the month to 12 and the number of the day to 31 and if i close the round brackets at that point and hit enter there's my new measure created i'm just going to change the way my table is grouped again i'm going to get rid of the country field this time and replace it with the certificate field.
And then i can add in my new measure average runtime 2000 to the table and there we go now that's not the only way to set the range of dates used by a filter argument dax has lots of functions for working with dates and just to show you one simple example let's replace the two separate filters we've created here with a single function called dates between you'll probably get a pretty good idea as to what this function is designed for so in place of those two existing filters well i'm going to use some of.
That code again so i'm not going to delete it just yet i'm going to add in the dates between a function so you can see that there are three parameters for this function the dates this is a reference to the column that provides the the list of dates and then a start date and an end date so the first argument i'm going to pass in a reference to my release date field then the second argument is going to be the start date so i'm going to say date 2000 comma 1 comma 1 and then the third argument is going to be the end date so that's the 31st of december 2000.
The end results of this will be the same and i'm not sure that's much clearer or certainly not much less code to do the same thing but the idea of using functions to return the range of dates you're interested in is an idea we'll be investigating in a lot of detail later on in the series so let's just update the measure and then if we check the results we can see it's the same as it was before just using a function rather than separate explicit boolean filters so we've seen a few things that we can.
Do with filters in the calculate function and i think everything's been fairly straightforward so far so let's move on and have a look at a couple of things that aren't quite so simple to achieve for the next example i'd like a measure that gives me the average run time of oscar-winning japanese films so let's start by making a copy of the average runtime winners measure we created earlier on as a nice starting point we can then create a new measure and then of course we'll need to add in.
A second filter alongside the oscar wins filter let's just change the name of the measure as well so it's called average runtime japanese winners we can add a second filter by adding a comma and then we can refer to the country field in the country table and that's where that is equal to japan now if i hit enter to create this measure everything will be fine i'm just going to modify the table again a little bit i want to get rid of a bunch of these measures so we can tidy things up a little bit so let's just go for the.
Average runtime and average runtime winners and then i'm going to change the grouping back to the genre field again so i've got the genre the average runtime and the average runtime winners and then i'm going to add in the average runtime japanese winners and at this point everything will be fine we can see which genres have got oscar-winning japanese films in them no problem now we did this in a fairly simple way we used a comma separated list of filters in the calculate function so the standard way of adding multiple filters combined with an and operator but what would have happened if we tried.
To do this using the alternative style where we try to write everything into a single filter using the double ampersand and operator everything looks fine but if we attempted to enter that measure we'll end up with an error the issue with this is that when you write filters in a calculate function the dax engine in the background rewrites those filters in a different way and when you try to pass a single filter which references multiple columns the dax parser isn't able to work that out so the entire thing fails.
Now we know this isn't a particular problem for this example because if we wanted to join filters with an and operator we can just write a comma separated list so that each column name we reference goes in a separate filter and can be dealt with individually but what if we wanted to find all the films that have either won an oscar or were released in japan then we do have a problem because the only way we can do that is to write the or operator making this into a single filter referring to multiple columns so again.
If i try to enter that that measure it won't work so we obviously need a slightly different solution to this sort of a problem the solution to this problem is to pass all the filters we're trying to evaluate into a filter function and then nest that filter function in the filter argument of the calculate function and fortunately it's a bit easier to do that than it is to describe how to do it so the job of the filter function is just to return a filtered version of a.
Table we're going to apply filters to the film table using these two criteria microsoft's best practice guidelines recommend not nesting the filter function as a filter argument unless you can't achieve what you're trying to do with using basic filters like this so this definitely qualifies as one of those situations so let's add the filter function as our filter argument for the calculate function you'll see there are only two parameters table and a single filter expression.
So the first thing we need to specify is that we're filtering the film table and then the filters we're going to apply are the two we've already written film oscar wins greater than or equal to one or country equals japan we can then close the extra set of round brackets for the filter function now you might notice another little problem we've introduced here the country column name can't be found and the reason for that is that when you specify the table you're filtering you can only directly reference columns that belong to that table.
The country column definitely doesn't belong to the film table it belongs to the country table to get to that country table if you think back to the database diagram or the data model that we looked at right at the very beginning of the video we may remember that there was a relationship between the film table and the country table and if we wanted to retrieve the related value we can use the related function to do that we've covered the related function and relationships in an earlier video so feel free to have a quick look back at that one if that part doesn't quite make.
Sense so having done that we can update our measure and this will fix the problem and eventually fix the color coding of our text there as well and we'll finally see the average run time of films that have either won oscars or were released in japan let's take a look at another example of where the filter function is necessary for the next example i want to calculate the average runtime of films but only include those which made a profit so to do that i'm going to compare the.
Box office dollars column with the budget dollars column and if box office is greater then i want to include that row in my results so again this is going to involve a filter expression which references multiple columns let's add the new measure first i'm going to right click and add a new measure to the measures table we'll call this one avg runtime profit or profits equals and then calculate and once again we'll make use of our average runtime measure i can then simply try to check whether.
The box office dollars column is greater than the budget dollars column but you can hopefully see if i just get rid of that tool tip this pops up that my intellisense isn't showing me the ability to reference a second column i can see the ability to select measures in this list but no other columns from a table so once again if i tried to persist with this it wouldn't work for the same reason that we couldn't refer to multiple columns in the single filter.
Expression in the previous example again then in order to make this work what we would have to do is nest the filter function in the filter argument of our calculate function so i'm going to nest my filter function in here i'm filtering the film table once again and i'm going to filter this based on the filter expression box office dollars greater than budget dollars and you can see now that the intellisense is giving me access to the budget dollars column so i can close a couple of sets of round.
Brackets there and then enter that measure and we'll see if we add that into the table we get the average runtime for profitable films one more example of using the filter function is when you want to reference an existing measure in a filter argument so for example let's create a measure that calculates the total number of oscars won but only for genres where the average run time is greater than 120. it's not the best example of a measure.
That we create but it's a nice demonstration of using the filter function again so if we try to do this the basic way let's create a new measure and i'm going to call this total oscars long genres or something like that i know it's not the best description total oscars for long genres and we'll make this equal to the calculate function and i want to calculate the sum of oscar wins now the filter i'd like to apply to that is to reference the original average runtime measure and check if the result.
Of that is greater than 120. and we've referenced the average runtime measure many times in the expression argument of the calculate function but trying to reference it in the filter argument is a little different we can say avg runtime and then check that that's greater than 120 and then if we try to enter that measure it won't work so once again the solution to this is to use the filter function to filter the genre table for those where the average runtime is greater than 120..
So once again we need to wrap the filter function in around that filter expression so we can say filter the table we're filtering this time is the genre table and then the filter we're playing is where the average runtime is greater than 120. so having done that we can enter that measure and then we can add that measure to our table and you can clearly see that it's only calculated results for those genres with.
An average runtime of longer than 120 the other ones return blank it's worth quickly mentioning that you can use multiple filter functions in the same calculate function remember the calculate function accepts multiple filter arguments so let's say we wanted to make a modified version of this previous measure to show the total oscars for long genres but only include films which were profitable so to do that we could let's grab a copy of this previous measure actually i'm.
Just going to copy that to the clipboard and then create another new measure and then we'll paste in all the code we've just copied and then just add the word profits to the end of the the measure name so we've applied one filter technically or one filter argument using the filter function there after that first filter function you can enter a comma and that moves you onto the second filter argument of the calculate function so i can say here filter and then this time we'll filter the film.
Table and we'll filter the film table for those rows where the box office value is greater than the budget value and close the extra set of round brackets so two nested filter functions passed to two different filter arguments of the calculate function so we can create that new measure and then we can add that one into our table and we should see a slight difference not a big difference but a slight difference there in the end results.
So we're excluding any films which didn't make a profit for the final part of the video i want to talk quickly about using the calculate table function which is very closely related to the calculate function the main difference is in what those two functions return the calculate function returns a scalar value which you'll see in the output of a cell in a table or another visualization whereas a calculate table function returns as its name suggests a table so it acts a little more like the filter function in.
That respect you'll find the calculate table function nested somewhere inside another function providing the table of results to operate on so just to get started with that example i want to calculate the average profit for all the films using the average x function which we've used in previous videos so i'm going to add a new measure to the all measures table and we'll call this one avg profit and we'll make this one equal to the average x function and the first thing we need to provide.
And here's a good clue as to where we're about to be using the calculate table function we need to provide a reference to a table so i'm going to refer to the film table and then the expression that i'm going to evaluate for each row is going to be the box office minus the budget and then i can close around brackets i'm just going to alter my capitalization there otherwise i'm not going to sleep tonight there we go that's a bit better and now that i've created that i'm just going to modify my table by getting rid of most of the measures i've added to it.