When to add a MEASURE and when to add a Column in DAX

measure column definitely measure definitely column are you also not sure when to use a measure and when to use a column in your dax let's take a closer look at this problem with a sample data model and then we will understand when you should be using a measure and when you should be using a column let's jump in so here i have a very simple data model with just two tables my sales data and my products data the simple rule for creating a measure is if you want to see any value on the screen here in your report or in your published outputs then you should be thinking of a measure for example here i want to see what is my total boxes sold if you go to the data model the table view here you can see that i have got a boxes column here and i just want to see what is our total boxes sold or break it down by a country we could put a column chart and pick the geography onto axis and boxes into the values area now even though we are not creating a measure at this point power pivot will create an implicit measure that would be sum of total boxes so either you're creating or not the measure is being made available so that this graph can be created so this is when a measure would be used now let's take the control of that process we can right click on the table click on new measure and define a measure for total boxes sum of boxes in the sales table and use that in the graph and you would be able to see the total boxes so anything that is shown on the screen is more or less coming from a measure whether it is an implicit or an explicit measure like the one that we created what about a column you would create a column if you want to see that as a physical column in the backend table now when would you need such a thing for example here i can see how many customers we have served or how many boxes we sold but we don't know how much money came through to find out how much money we generated we would need to go to the products table notice that each product has a price per box as well as a cost per box so i can use this information to figure out for example here my milk bars are 12.9 dollars per box and if i go to the sales table and notice a milk bar here we sold 45 milk bars 45 boxes of milk bars so the total money would be 45 times 12.9 so this is how we can calculate the total sales so if i want to see the total sales against each transaction as a value in the table then we would create a column so you can create the column from table column new column and this column will be my sales is equal to number of boxes times related product price per box because the table products is linked to my sales table through the date model i can simply use this kind of a syntax and it will basically do the multiplication for each row 422 times price per box for almond choco will go here and now that this sales information he is here in the table physically i could then again use that as part of my outputs for doing a total or an average or something else so that is when you would use a column now what happens if you can use either a measure or a column to get the same result in that situations what should you be doing well let's take an example here here i got both my sales and cost added as two separate columns sales is my boxes times related price per box and cost is my boxes times related cost per box so now that sales and cost are available you might be tempted to create a third column called profit to calculate profit per each row which is nothing but sales minus cost but if you want to just see the total profit then you don't have to do it like this you can do it here as a measure so for that first thing that you need is you need to create two measures one is total sales which is sum of the sales column in your sales table and then we'll create another measure total cost this is equal to sum of cost column in the sales table so now that both total sales and total cost are available we could create a third measure which is total profit this is equal to total sales minus total cost and that will give you the total profit value and here i can just see total profit in each country as a visual that's my total sales of one million dollar and profit is here so notice that here we could have added a column called total pro profit here and then added that up but you don't need to do that you could simply use a measure to calculate that we can extend this argument and even write a measure that will tell you total sales directly even if that column is not there let us do that so we will use a new measure i'm going to call this as total sales 2. this measure will calculate the total sales value directly without using that extra column so this is a measure that needs to add up sales for each row in the sales table by doing an operation the operation is it needs to go and multiply the number of boxes in that row with the related product price per box so this is where you would use an iterator function like sum x because we want to sum up all the values so sum x is the function that we will use in the sales table for each row in the sales table we want to take sales boxes and then multiply that with the related products price per box when you write this sum x function it kind of mimics that exact add column functionality but it will do the operations all in memory and set that value into the measure directly completely by passing that extra column option and you can use this in the graphs and it will match the exact result but this thing is not relying on that physical column so at this point you might be again thinking oh this is all good i could do the same thing with either a measure or a column again i'm back to my original question when should i use the measure when should i use the column well the rules are very simple if you need to see the value only on the screen then try to do it with measure as much as possible but if you need to see the value either both on the screen as well as in the table or just in the table then you will need that as a calculated column because in this case we could argue that we don't need to see what is the sale level at each row all we need is an aggregated value of total sales and total cost we should be building measures like this instead of adding a column but if you want to for example set up a slicer using which i can select one of the values then without having a field in the table we will not be able to set up a slicer so you must add a column for that okay now that we have resolved the confusion between measure and calculated column hopefully you'll make a better choice next time if you want to understand how power query power pivot and power bi interact with each other and how they all flow together then here is a video that i did a while ago that explains the concepts better check it out and here is a video that youtube thinks you will enjoy i'll see you in one of these two places bye [Music] you

Channel: Chandoo

Views: 33,220

Keywords: chandoo, chandoo.org, power bi, when to use powerbi measures, measure vs column power bi, measure vs calculated columns, power pivot sumx example, iterator functions in dax, dax sumx, calculated columns in dax example, can I replace a calculated column with measure in power bi, how to replace columns with measures in dax, measure vs column best practice, power bi interview questions, power pivot interview questions, dax questions in interviews, when to use a measure vs. column

Id: kgNbWOCuRCE

Length: 7min 59sec (479 seconds)

Published: Tue Nov 09 2021