HOW and WHEN to use Measures and Calculated Columns // Beginners Guide to Power BI in 2021

in this video i want to go through the key differences between measures and calculated columns we're going to go through what i think are there three key differences as well as when and why you should be using one or the other all of that and more so without further ado let's get started hi my name is fenan and welcome to the solutions abroad youtube channel where i focus on teaching beginners the wonderful world that is power bi i upload new videos every week so make sure you hit that subscribe button and the bell icon to get notified when a new one is out i wanted to cover this topic today because one of my colleagues asked me what are the differences between using measures and calculated columns and it's a perfectly valid confusion to make especially if you're using them and having the same results on both so it's probably easier if i can show you some examples and show you the key differences between one or the other so here i've created a data set of orders and customers if we go to the data quickly you'll see that we have some information about different orders by different customers and we have customer details like their contact name and in the data model itself we have a one-to-many relationship between the customers table and the orders table and if we go back to the report view here you'll see that i've already created two values here so i've created total sales for calculate column and total sales as a measure and i've just put them in a screen here just to show you where the confusion lies so when you look at this two cards here that we have on the report although they are measure or calculated column they have the same value so you might be confused as to which one you should be using or you can use either or so the first difference between the two is how they're calculated if you imagine calculated columns they are pre-calculated and stored in your model as rows so you can see on the table here if we go to the order details you'll see that the total sales calculated column shows up as a as a separate column in this table that you have here but unlike the calculated columns measures are not pre-calculated in your model it's actually calculated in real time so it only calculates whatever it needs to calculate when you use them in your report and it recalculates every time you introduce filter context on it and a key thing to note here is that you can see that we have the total sales measure in the order details table but you can't see it here as a row because it's calculated real time and it's not stored in your data model so here the general rule of thumb is if you want to keep your data models small and manageable you want to be using measures as much as you can the second difference between the two is how they're stored so as i showed you before because the calculated columns are stored as rows within your tables it means that these columns already has a row context which means that you can implicitly apply aggregates on them on your reports so if you go to our report view here and let's go back to this calculated column measure here and you'll see that if i click this button here to change the aggregation of the fields here you're able to apply implicitly what aggregation type it should have and at the moment it defaults into a sum which is what gives us the total sales value that we have here but for example you can change it with using other aggregates like average minimum maximum or count things like this measures on the other hand doesn't understand raw context so it means that if you want to aggregate the values in your measures you need to explicitly define how your values are being aggregated so you'll see the difference here if i go to the total sales measure and i click this arrow again the same you'll see that it doesn't show us the aggregation types that you know we had in the calculated column and we say we define the aggregations explicitly because if we go to the total sales measure calculation you'll see that we use a sum x iterator function here which does the aggregation for us so it means that we're explicitly saying for the total sales measure sum up all the values that you get from these unit price and quantity and just to compare this with the total sales calculated column you'll see that we don't explicitly define how it's aggregated so we don't say summer all we say is give me the value the sales between unit price and quantity and in order to show it here as a card we have to implicitly say sum it all up another thing to note with the measures is that because they don't have raw context it doesn't matter where they're stored in your data model so at the moment you'll see that we have the total sales calculated column in our order details table and we really can't move it around without breaking anything but with the total sales measure if we go to the data view or the model view and you'll see that if i move the home table from the order details to customers so just moving the measures around that won't have any impact with our calculations at all and you'll see if we go back to the order details and see if we can move the column you won't have the option to move that when working with calculations in power bi it's best practice to explicitly define how your values are calculated within your measures this makes sure that if anyone uses your calculations in the future they'll know exactly what the behavior to expect when they're using it in their own calculations the third key difference that i found is there are other utilities so because a calculated column has raw context it means that you can use it as a filter value in your reports however because measures don't have a raw context you can't use them as filters in your report so let me show you the difference here's an example that i already created it's a calculated column of customer names and what i've done is i've used the related function to bring in the customer contact names from the customers table into the order details table and because this customer name is a calculated column we can go to our report and bring it in here and use it as a filter so if i convert this into a filter if we want to see what are the total sales by customer we're able to get those aggregations with no issue because calculated columns have raw context you can also use them to create relationships between multiple tables which you can't do with measures so if you go here in the model view you'll see that you have the total sales calculated column which you can use to create relationships between tables however if you try to do that with the measure you'll see that you aren't able to you know create that relationship you can't drag it around because it doesn't have a real context for you to use now that you know what the differences are between measures and calculated columns let's look at some examples and see when you should be using measures or calculated columns so if you're doing calculations like calculating total sales the general rule of thumb is you should be using measures this is because you want to be explicit as i said before you want to be explicit with what the behavior is of your calculations and using measures actually forces you to be explicit about how your calculations are done so let's do another example let's say you wanted to bring in the customer names in the orders table from the customers table and to create this kind of lookup field you can only do it with calculated columns because measures don't have the same raw context that calculated columns have so let's do another example if you wanted to group your orders based on the number of sales so you want to create the grouping based on uh low medium or high sales you want to be using a calculated column for this kind of columns this is because the groupings need to have a raw context in order to use them as uh filters within your reports so let's try to create a quick example here so let's create a new calculated column here and let's create a grouping let's name it group by sales and let's do an if logic here so if the um if our sales is greater than 500 pounds we will do we'll make it high otherwise we want to give it low so it's a very simple if and else logic that just categorizes our groups our sales based on their sales now if you used it as a calculated column you should be able to drag it here as a filter so if you wanted to see what the totals are for those you are able to select one or the other and that's really it for this video i hope it helped you clarify what the differences are between measures and calculated columns in power bi leave a like on this video if it helped you it's the best way to let me know that you enjoy this type of content get in touch using the social media links that i included in the description box below and thank you so much for watching guys see you again on the next one

Channel: Solutions Abroad

Views: 16,268

Keywords: solutions abroad, power bi, powerbi, power bi tutorials, power bi for beginners, beginners guide to power bi, data analytics, dax, data modelling, data visualisation, business intelligence, power bi 2021, power bi dax, power bi measures, power bi calculated columns, power bi measures calculated columns, power bi explicit, power bi implicit, power bi measure vs calculated column, dax best practices, dax tips and tricks, dax tips, dax grouping, dax calculations

Id: ns4VbpRKAFs

Length: 9min 47sec (587 seconds)

Published: Fri May 14 2021