We'll show you how to use both the and function and and operator and then the or function and or operator to combine multiple conditions in the same logical test towards the end of the video we'll explain how you can use the in operator to combine multiple or conditions in a slightly more succinct way it's quite a lot to do in this one let's get started to get started i've created a new report and the first thing we'll do in here is import some data from an excel workbook as usual i have several choices for how.
I can do that i'm going to click the excel workbook button up here on the home tab of the ribbon i'll import my standard list of movies and again i'll drop a link in the video description so you can download this file yourself i'll double click on the file and then when the import wizard loads i should find a single worksheet called movies i can check the box next to that click the load button and that data will then be imported when that's finished just so that we can see the results of the calculated.
Columns we're going to create in this video we'll switch into the data view by clicking the data button on the left hand side and then we'll get ready to start creating our calculated columns for the first example i'd like to create a column which lets us categorize our films based on the value of oscar wins so i'm going to describe my film as either a winner or a loser depending on whether the oscar wins value is zero or not it's a fairly harsh system i'll admit but hey that's life in hollywood it's a harsh and unforgiving place.
So let's get started by clicking on the new column button to create our new column and then just so you can see what i'm typing i'm going to zoom in on the formula bar by holding down the control key and rolling the mouse wheel forwards remember if you don't have a mouse wheel you can hold down the control key and press the equals key to zoom in if you need to we'll change the name of the column columns are fairly boring and inventive name much like my other column names let's call this one oscar success and then after the equals operator we can begin our if function and the.
Simplest way to do that is to type in the letter i which will highlight the if function in the intellisense list and then you can press the tab key to type in the rest of the function and expose the tooltip telling you how the function works so from this we can tell that the function has three parameters logical test result if true and a third optional parameter we know that it's optional because it's listed in square brackets result if false i'd like to write my logical test on the next line in the formula bar so to throw.
Then if i hit tab when my column name is highlighted to type in the rest of that reference and then i state that that is equal to zero i'm then going to type in a comma and then shift and enter to move on to the result if true parameter so if that logical test is true i want to return the word loser in dax we must then close text in double quote characters so in some double quotes write the word loser now i don't technically have to provide a value for the result if false.
Parameter i could just close my round brackets here and press enter to create my formula in fact let's do that i'm going to press shift and enter and then close the round brackets and then enter by itself to create my formula and that will work and you'll see that for any films whose oscar wins is zero they're described as losers rightfully so if i scroll down far enough you'll find that when we hit the films that do have oscar wins we've returned a blank the equivalent of null if you're familiar with nulls in sql i'd prefer to provide a description for.
Films which have won some oscars so i'm going to click back into my formula bar i'm going to type a comma after the word loser press shift and enter and then on the next line type in the word winner in some double quotes i can then press enter again to update my formula and this time we'll see that when we hit the films that do have some oscars they're now described as winners now of course the point of creating extra calculated columns in your data model is so you can use them in some.
Visuals in the report so let's just take a brief moment to head back to the report view by clicking on the report button in the top left hand corner and i'm going to build a basic chart to display the average runtime of films in different genres divided up into winners and losers so i'm going to begin by creating a clustered column chart i'm just going to collapse the filters panel just to give a little more room to design my chart i'll resize it so it fills the full screen and then i'm going to check the box next to the genre column in the fields list.
To add that to the axis bucket then we'll add in the runtime field to the values bucket by checking the box next to that i don't want the sum of runtime i prefer to have the average so clicking on the drop down arrow next to the runtime field in the values bucket i can switch that to say average finally i'm then going to find my oscar success field and i'm going to drag that into the legend bucket so i can drag oscar success into legend and that will automatically divide each genre into.
Losers and winners according to the results of our if function
So we've used the if function to create a category column that we can use to slice our data up into different groups but we can also use the if function to create value columns for the next example we're going to calculate a tax for each film based on the value of the box office column so it's a fairly harsh and unforgiving system yet again if the value of box office is at least a.Billion dollars we're going to collect 20 of that figure as tax otherwise we'll only collect 10 percent so to make that work let's create a new column back in the data view which we call this column we could call it tax we could call it dax tax now it's a silly name let's not call it dax tax let's just call it tax i can't bring myself to type in dax tax but it's going to use the if function again so type in the letter i after the equal sign press the tab key to type in.
The rest of the function name and open the round brackets then shift and enter and i'm going to start typing in the word box for box office hit the tab key to type in the rest of that column name when it's highlighted and then i want to check if it's greater than or equal to 1 billion so i have to count out the number of zeros i type in one two three four five six seven eight nine okay then i can type in a comma and then on the next line if i press shift and enter.
I don't just want to present some text this time i want to perform another calculation so i'd like to do to multiply the value of the box office column by 20 or 0.2 so i can do that by saying box office multiplied by and then if you're familiar with excel you may be familiar with typing in things like 20 so you can actually type that into your formula that doesn't work in dax in dax we need to use the underlying decimal value for 20 so 0.2.
Then i can type in another comma shift and enter go for the box office column again and multiply that by 0.1 for 10 shift and enter close the round brackets press enter to create the formula so just to try to demonstrate that this is working correctly it's a little trickier to see than the basic example we've just done there if we sort our box office column in descending order so we can click on the drop down arrow at the top of the box office column sort in.
Descending order we'll hopefully see that any film whose box office is at least a billion dollars we collect 20 of that figure but for any film whose box office is under a billion dollars we only collect 10 percent when you've created a calculated column you want to use as a value in a visual there are a couple of settings you might like to change before you start using it.
In the report view so for example with the tax column selected we could apply a default format to that column using the formatting part of the column tools tab of the ribbon so i'm going to change the formatting here to dollars english united states i could also choose to apply a default summarization so when i add this column to a visual rather than automatically summing that column i want this to be averaged instead so for the summarization option choose average then if i head back to the report view.
We could simply replace the values column in our charge or the values field in our chart from average of runtime let's get rid of that i'm going to drag the tax field into the values bucket instead and that'll show us the average tax generated for losers and winners for the different genres of films so far our logical tests have been based on the value of a column but you can also base your logical tests on the result of an expression that you nest inside the a function so just to demonstrate that let's change.
Our tax system so that it's not based on the gross box office value is based on the profit that the film makes so we're going to calculate the net by subtracting the budget from the box office just to show you that you can change your expressions for your calculated columns in the report view let's just highlight the tax column from the fields list by clicking on it and that will expose the formula bar i'll have to zoom in again because the zoom level for the data view and the report view are different so i'll just zoom in by.
Holding the ctrl key and rolling the mouse wheel forwards so i'd like to modify the expression
Movies box office to say movies box office minus movies budget and then i want to calculate 20 of the tax on that expression and 10 percent of the tax don't tax on that expression so i don't want to have to repeat myself when i've edited the expression a neat little trick with the dax expression editor if i click at the beginning of line two and then hold down the alt key.On my keyboard and click in the same position on the subsequent two lines i get three active text cursors and if i begin typing if i open up some round brackets for example that same character gets typed in on all three lines i can then reposition the cursor using the arrow keys on my keyboard and then after where it says box office i can type in a space then a minus symbol and another space and then i can start looking for the budget column now one small downside of this little feature is that the.
Intellisense doesn't quite work as well as it should so i don't get any help from the intellisense to insert my columns so i'm going to type in movies open some square brackets budget and then close the square brackets and finally close the round brackets so the same expression now nested on each line in each parameter of my if function if i simply press enter at that point that will update the expression and you hopefully saw the chart updating its values in the background there as.
Well so nesting expressions inside your if function now as well as nesting basic expressions like this inside your if functions you can also nest entire if functions inside your if functions and that's useful whenever you want to provide more than just two possible answers so to demonstrate how this works let's head back to our fairly unfair oscar success if function i think it's a little unfair to describe a film a loser if it could not have even.
Won an oscar so if it hadn't received any nominations we should maybe describe it as a not nominated film and then only if it received at least one nomination could we divide it into losers and winners now we can arrange this combination of their functions in lots of different ways i'm going to put this entire if function that we've currently created inside the false part the answer if false argument of another if function so after my equal sign at the beginning.
Of this expression i'm going to type in if or at least i again hit the tab key to insert another if function then i'm going to press shift and enter a couple of times to move this if function downwards and then in the blank line i have for line number two i'm going to test a different condition so i want to check the nominations column so i'll start typing in nominations highlight movies nominations and then ask if that is equal to zero.
I'll then type in a comma press shift and enter and if that condition happens to be true i want to describe the film as not nominated so it couldn't even have won an oscar and i'll spell it correctly eventually there we go then i'm going to type in a comma which moves me onto the result if false parameter of the outer if function when we reach that parameter i want to then check if the oscar wins value is equal to zero so we're only going to.
Reach this stage here we'll only evaluate this condition if the movie's nominations is not equal to zero just to tidy up the layout i'm going to highlight all of these lines of code from lines five down to eight and hit the tab key to indent them all one space so that entire riff function is at the same tab level and then i'll need one additional closed round bracket symbol to go along with the outer if function so i'll press shift and enter at the end and close.
Another set of round brackets okay so having done that we can press enter or click our tick to update our expression and once again we should see in the background that we've now generated a new result for the oscar success column and that's replicated in the chart with the new not nominated category next i'd like to show you that you can combine multiple conditions in the same logical test and to demonstrate that we're going to create another column that describes a film's performance in more general terms.
Not just based on the oscar wins or nominations so i'd like to describe a film as a high performing film if it made at least a billion dollars at the box office and it received at least three oscar nominations so to do that let's head back to the data view this time and i'm going to create a new column by clicking the new column button and let's call this new column let's call it performance or general performance i suppose.
I'm going to write an if function again so type in the letter i hit tab followed by shift and enter and then i want to combine two conditions in the same logical test i've got a couple of ways i can do this and for this example i'm going to use a new function called and so if i start typing in the word and i can then hit the tab key to insert that and you'll see that in the tooltip i can add two separate logical tests in the two separate parameters and if every single one of those logical tests returns true the entire function returns.
True so my first logical test let's hit shift and enter down to the next line i want to check if the box office column has a value that is at least one followed by one two three four five six seven eight nine zeros then i can type in a comma and then shift and enter and then i want to check if the nominations column contains a value that is at least three so greater than or equal to three i can then close the round brackets.
Type in a comma shift and enter and if both of those conditions are true i'd like to call the film a high performing film or a high performance let's just write the word hi it's much easier to type then i'll type in a comma shift and enter and let's call this what normal and then one more close round brackets to close the if function off if we then press enter we'll see we get a new column and any high performing film must have met both conditions so.
Nominations must be three or more and box office must be a billion or more so the and function is great providing we only want to combine two separate logical tests but what if we wanted to add a third i don't think we can really call a film a high performing film if it didn't actually win any oscars so i think we should add a third condition to make sure that we received at least one oscar win now there's no way to do that inside this current and function if i click at.
The end of the and function there you can see it's run out of parameters after the second one if i typed in a comma there are no more parameters to fill in so i suppose we could nest and functions inside and functions if we wanted to expand on this but that gets a little bit messy and i think there's an easier solution although not a particularly obvious one i think so i'm going to start by getting rid of the and function let's just get rid of the and and the open round brackets and.
Bring our first logical test back to line number two then i'm also going to get rid of the closed round bracket corresponding to the end of the and function and bring the comma back to the end of the second logical test then let's just get rid of that tab level there as well so at the moment it looks like i'm trying to specify four different parameters for the if function and that certainly won't work so what i need to do now is make sure that the comma in between the two logical tests is removed.
And replaced with the and operator which in dax is the double ampersand symbol so as long as this condition is true and this condition is true then we'll return the word hi now the great thing about this is i can combine as many different logical tests as i like using and operators so after the second logical test i can type in a double ampersand and then on the next line let's go for oscar wins and check that that is at least equal to one and i could carry on going there.
Anyway that's enough for me i think if i press enter to update that expression we'll now see we've reduced the number of films which is described as high performing ones to only those which match all three conditions we have similar options if we want to combine multiple conditions on an ore basis and to demonstrate that let's try to help our users to avoid terrible movies in our list i want to classify a terrible movie as one which either has a genre of awful or a director of michael bay sorry.
Michael bay fans and feel free to substitute michael bay for another terrible director of your choice oh jj abrams um anyway whoever you choose will use their name for this example so let's create a new column i'm going to call this one what should we call it let's call it recommendation or wisely recommendation equals if and then on the next line to begin with we'll use the or function so this operates just like the and function if i say or i've got two separate logical.
Tests to insert so the first logical test i want to check is the genre field equal to the word awful now comparing strings like this in dax isn't case sensitive so i can write awful with a lowercase a and still successfully return a result i can then type in a comma and then on the next line let's refer to the director field and check if that is equal to your own personal least favorite director so i'm.
Going to go for michael bay and then i can close the round brackets for the or function type in a comma and then on the next line i'm going to provide a recommendation of avoid and then another comma and then i can't guarantee that the film will be watchable but it's probably safe to watch let's let's give it a cautious recommendation of probably safe i can then close around brackets for the if function and then hit enter to find my.
Recommendation many probably safe films but there's a michael bay movie to avoid and another one and if we just want to see all of the films to avoid we could sort the avoid or the recommendation column in ascending order and all of the movies to avoid will sit at the top of that list just as with the and function the or function is limited to just two logical tests so if you want to combine more than two conditions using an or then you need to.
Use the or operator rather than the function so let's do the same as we did with the and function we'll get rid of the or function and then get rid of the close round brackets at the end and take the comma back to the previous line get rid of that extra tab space and then we can replace the comma in between our two logical tests with the or operator which are the two vertical bar symbols or the pipes so again i could combine multiple conditions with multiple or operators.
Just as we did with and but i'm going to keep this one to just the two press enter to update the expression and get the same end results now i feel that i've been a little bit harsh on michael bay here because not every single one of his films is absolutely dreadful just most of them but things like the rock and bad boys and bad boys too are quite watchable so i'd like to modify our condition a little bit to only consider films directed by michael bay in the science.
Fiction genre these are the ones that tend to be pretty dreadful oh and pearl harbor of course that's unequivocally awful at all times so maybe we should avoid that one as well but i want to add an extra criterion to this condition if a film is directed by michael bay and it's in the science fiction genre then it should be avoided or if the movie is in the awful genre at all times we all know to avoid twilight movies that's just generally good advice for life so let's expand on our conditions a.
Little bit i'm going to add an open round bracket in front of the condition that checks if the director is equal to michael bay when you're combining multiple combinations of ands and ores is always worthwhile wrapping round brackets around the combinations of criteria you want to evaluate together so if the film is directed by michael bay and in the science fiction genre then it's to be avoided so i've got movies director equals michael bay then on the same line i'm going to say double ampersand.
Movies genre equals and then go for science fiction i'm going to run out of space on that line let's just zoom out a little bit so we can get everything onto a single line hopefully that's still readable for you as well so i've closed around brackets there i think there's one more thing we should do i want to check that the film's title equals pearl harbor so i've got movie genre equals awful or the director is michael bay and the genre is science fiction and then one.
More or operator if i add another double vertical bar and then i'm going to check that the movie's title equals pearl harbor i've got to spell that incorrectly because for some bizarre reason they've spelt it without the u anyway there we go so having done all that let's click the tick or press enter and we should get a modified list of movies to avoid but it means that things like the rock and bad boys you can now safely watch according to wisel's recommendation.
For the final example let's be a little more positive i'd like to create a column to label any film made by one of our favorite directors and we can choose multiple different directors for this example i'll start by creating a new column and then what should we call this one let's call this one fave directors or something along those lines and i'll start by saying equals if and then to begin with i'm going to use the or operator to check if the director field equals one director's name then a different director's name and so on.