in this tutorial I'm going to get you started writing your own Dax formulas in both powerbi and Excel I'll cover where you can find a complete list of functions available wearing how to write Dax formulas and teach you the fundamental concept of context which is key to being able to write Dynamic Dax formulas Dax is the power pivot formula language available in both powerbi and Excel it stands for data analysis expression as you you can see Dax functions are very similar to excel functions so it's relatively easy for us Excel users to pick up we use Dax formulas to perform Advanced calculations on data in related tables and columns in power pivot for Excel and power pivot in powerbi so what is power pivot I like to think of power pivot as a mashup of excel pivot tables and access if you're familiar with both of these then you've got a head start and you'll instantly recognize the access features available in power pivot power pivot stores data in Separate Tables and just like access and other relational databases we can build relationships between the tables which enables us to write Dax formulas measures enable us to analyze the data in a pivot table or pivot chart in Excel or in tables and visuals in powerbi you'll find a complete list of Dax functions here and I'll pop the link in the video description there's over 200 50 functions and the list is growing all the time each function page has a description syntax parameters remarks and an example now it's important to note that not all the functions available in powerbi are available in Excel and unfortunately the documentation doesn't tell you if a function isn't available in Excel the only way you'll know is to try it and if the intellisense doesn't autocomplete the function name as you type then it's not available for example here in palbi I can type in remove filters we can see the intellisense offers it to me in the list and I can tab to select it and start entering the formula however if we try that same function in Excel you can see once I get to M for remove the intellisense list goes away and if we scroll down we can see that we've got related table replace and then repeat but no remove filters so we know this function isn't available in Excel but don't worry too much if a function isn't available because there'll be a workaround that enables you to perform the calculation using different functions all right let's look at some Dax formula examples and we'll do that starting in powerbi now Dax formulas are used in measures calculat columns calculator tables and row level security in this tutorial we're going to focus on writing Dax measures because measures are what you use in your powerbi visuals and Excel pivot tables in both Excel and powerbi there are a few entry points for writing them here in the powerbi report view we can write measures by the Home tab and you'll also find it on the modeling Tab and if you have a table selected we also get the table tools tab the contextual tab and we have new measure on there if we go into the data view you have the table tools tab with new measure and if you have a measure selected as I do here we also have measure tools with new measure the other way we can create a new measure is by clicking on the ellipses for a table and then new measure any of these open the formula bar where we can write our measure now my model contains it cost data by Department region and business area let's say I want to write a measure that sums the forecast cost so let's give it the name forecast cost or costs and then we can write the formula we'll use the sum function we want to sum the forecast column of the forecast table so I'm just going to tab to select it and close parentheses we can see the forecast column there contains our forecast values I can either press enter or click on the check mark to complete the measure and notice the formula looks very similar to an Excel formula it even has the same function name we're familiar with in Excel and if we look in the field list we can see our new measure here and it's denoted by the calculator icon we can use this measure in our report view so over here we can see our forecast Table and there it is there if I check the box powerbi will automatically insert the column chart I can change the visualization type a cluster bar chart might be better for what I want let's look at it by it area so I'll check the box for it area and you can see my forecast costs are broken down by the it area without me having to write a different formula now this is a simple example let's switch to Excel and see where to write measures there as well as understand the fundamental concept of filter context in Excel we can write measures by the power pivot tab measures new measure or we can open up the power pivot window via the manage button and write a measure anywhere in this bottom section here which is also adjustable so if you have lots of measures you can make it bigger let's write the same forecast measure here I'm on the forecast table so here I want forecast total and notice it's right in the formula bar now in Excel it's a little bit different I start with a measure name and then immediately after without any spaces I enter a colon and an equal sign and then the function and again we want the forecast from the forecast table close parentheses here I can hit enter or the check mark so there's my forecast total it's currently showing me a number here and that's essentially the total of this column let's give the measure some formatting so that when we use it in the pivot table it's automatically formatted I don't need to do anymore so remember I wrote this on the forecast table so now when we go back into Excel if I insert a pivot table from the data model and we look at the forecast table you can see there's my measure there it's denoted by the function icon telling me that this is a measure so let's build a pivot table using this new measure so we can get to grips with the fundamental concept of fil context so it's currently showing me the total forecast now at this level there are no filters applied it simply shows me the total of the forecast column let's also break it down by it area and as I add fields to the pivot table the measure recalculates just like it did in powerbi I haven't had to rewrite the formula let's also add a filter for the cost element group and I'm going to filter it for labor now my pivot table is showing the forecast for labor by it area in other words the forecast total measure I wrote is respecting the filter context of each cell in the pivot table and that enables me to write one formula and have it dynamically adapt to return multiple results as I make changes to my pivot table to label the point if we take one cell as an example say enablement the Dax formula here is being filtered by the row label enablement and the filter for the labor cost element group and so on for each cell in palbi it works in the same way so here I can add in a slicer for the cost element group let's just bring it up beside the chart and I can select labor you can see my charts automatically updated to filter the data for labor I haven't had to rewrite the measure now what we've seen so far are explicit measures that is measures that we write ourselves explicitly however both powerbi and Excel can write measures for you and these are called implicit measures now we do this anytime we use a field in a pivot table chart or the visuals values area in pobi in pobi fields that contain the sigma sign are typically the ones you'd want in the values area of a visual so for example here if we look at the budget table and we select the bar chart we can add the budget to the visual now I haven't had to write a measure there powerbi has implicitly written me a measure and I can click on the drop- down arrow here and change the aggregation method from the default which is sum now implicit measures also respect filter context as you can see it's broken down by the it areas if I change the filter context here and change the cost element group to hardware and software you can see both measures have updated respecting filter context we can do this in Excel as well so let's go and take a look here let's say I want to see the budget amount let's drag it into the values area and there we have the sum of budget I haven't had to write a measure Excel has automatically written it for me in Excel you can change the aggregation method by right clicking and then summarize values by and we get the same options here as we see in palbi and in more options you can see the full list so far we've looked at some really basic measures and you may have noticed that we didn't even need to write them because we could simply use the implicit measures available when we drag Fields into the values area of the pivot tables and visuals but typically you'll be writing more complex measures where you Nest functions just like we can in Excel for example let's say I want to calculate what the percentage the forecast is of the total budget so for each it area's forecast I want the percentage these values are of the total budget and for that I'm going to need a new measure that's going to return this total budget on every row so let's go ahead and do that by the power pivot tab measures new measure I actually prefer to write my measures in this dialogue box here let's make it a little bit bigger we can choose the table that it's associated with we'll leave it attached to budget and we can give it a name budget total we can give it a description I'm going to leave that blank it's fairly self-explanatory now I can use the calculate function and that's going to enable me to modify the contextual filters let control and mouse will to make it bit bigger now the first argument for calculate is the expression that is what result what value do we want to return I'm just going to shift and enter to bring that down onto the next line and for this we want to sum the budget total so the budget column of the budget table I want to sum the next argument for calculate is the filter now in this case I want to remove the contextual filters for the it area and I can use the all function for that all ignores filters for the specified table or column so in this case I want to ignore the filters for the it area so that's going to mean my argument for all and then all I need to do is close parentheses on calculate let's check the formula we get the check mark perfect let's give this currency sign and no decimal places I'll click okay it automatically gets added to the pivot table we can see it there in the field list for the budget table so now I can divide business area supports forecast by the budget total and calculate my percentage now I could write another measure for this but I'm going to modify the one we've already written so instead of budget total let's edit that and we'll give it a different name this is actually going to become my forecast percentage of total budget and let's control and and mouse will up to make this a bit bigger and easier to read so the function we're going to use here is divide the nice thing about divide is it automatically hides any division by zero errors the first argument is the numerator and that's going to be my forecast total we already have written that measure so let's use it there so in this formula we're referencing a measure and we're nesting some other functions let's put a comma in there the next argument is the denominator that's my budget total which I've already written with the calculate formula so all I'm left to do is close parentheses on divide let's check the formula we get no errors now this time we want it to be a percentage and let's give it one decimal place click okay and close and now it's converted the field to my amended measure which shows me the forecast percentage of the total budget we can see there that we're nearly 2% over budget based on the forecast we can see the breakdown of the percentages here you can see that Dax formulas are very similar to excel formulas in the way they're written of course the major difference is the filter context and how those formulas behave when you use them in pivot tables or powerbi visuals for example I can insert a slicer for the cost element group and as I changed the cost element group in the slicer you can see the measures automatic update without me having to rewrite the formulas I hope you're excited to get started writing your own Dex formulas if you'd like to learn more Dax and power pivot please consider my power pivot and Dax course you can download the file for this lesson from the link here and if you like this video please give it a thumbs up and subscribe to our channel for more and why not share it with your friends who might also find it useful thanks for watching [Music]