DAX by examples

ciao friends and welcome to a new video from sql bi i'm alberto ferrari and i'm a business intelligence consultant and a dax trainer i spent most of my time teaching daxa to different students all around the world in this video i want to show you a beginner guide to dax through examples when you are a newbie in dax you search for examples on the web because you have the idea that by looking at a lot of examples you can increase your knowledge about dax and learn the details that are needed to solve your scenarios the thing is the kind of examples that you can understand at the beginning they are simple examples so there will be just uh one simple dax expression that contains one or two formulas that solve a specific problem and you might derive the wrong idea that learning dax means learning the details about all the functions in dax as if there always were a function to solve your specific problem the real world is quite different most of the times your specific scenario requires combining together different functions in complex tax code so that your unique question is actually answered in order to build this dax code you need to learn theory first and understand it well i want to show this with an example therefore what we will do in this video is not just showing it's not just looking at different functions and how they work that would be too simple and in some way misleading instead i want to build with you quite a complex dashboard that requires calculation for which we do not have any specific functioning tasks we will need to build the code and i will keep the theory to a minimum nonetheless let's start by looking together at the final result we want to obtain then we reason a bit on top of the entire video and then we start writing dax code we are going to work with the contoso model let's start by quickly looking at the model okay this is the data model we have contoso sells goods all around the world and the sales are stored in the sales table you can buy your goods in a store we have different stores all around the planet and we also sell online and of course sales happen to customers there are also other tables like date and products but we are not interested in those tables for now the most important thing are store and sales for now if you buy in an online store if you buy in a physical store then what happens is that you enter the store grab your goods pay for them and you're happy therefore the delivery of the good is the same as the order date they will happen at the same moment but if you buy in an online store then you place your order and it takes some time to deliver this good therefore we have in the sales table two dates which are the most relevant dates for this demo which are the order date and the delivery date this first order has been placed on the 22 of february but it was delivered on the 25th of february therefore it took three days to deliver the order and we want to do an analysis of how good we are in delivering orders in different countries all around the planet so the final dashboard that we are going to build is this one that shows the cumulative percentage of deliver orders by the number of delivery days let me show you the details about the dashboard okay we have on the columns the number of days required to deliver an order one day two days three days five days then we have the different countries where we ship our orders and here the number the first cell 16.53 means that 16.53 of the total number of orders has been shipped in one day only then we have two 39.67 this is a cumulative percentage meaning that this number 39.67 includes the orders shipped in one day and two days and the number always grows so here for seven that says that 95.59 of the orders in australia have been shipped in at most seven days then when we reach a hundred percent we know that all the orders have been shipped in at most 10 days dashboard also needs to color the cells you see that we have some cells that are colored in blue some others that are colored in red and the reason for this is because we compare each cell with the global percentage the blue cells are above the global percentage whereas the red cells are below 12.90 is below 14.95 so we want to provide a visual color a visual information to the user that quickly shows whether in a specific country we are better or worse than the average finally talking about the dashboard you see that when we start when we reach a hundred percent we stop showing further values once we have reached a hundred percent for seven days in germany italy and the netherlands we do not show further values that makes it easier to focus on the numbers where they are relevant building the dashboards will require some work we will need to first compute the number of days which are required in order to deliver an order then we will need to slice and compute the percentage of orders delivered in different days and finally color the dashboard the right way there's a lot of work to do and as you might guess there are no dax functions that will help us with a single line of tax code we will need to write it together i will keep the theory to a minimum the minimum required in order to understand the code that we are writing with that said i will explain some theory because the basic theory of the row context the filter context and the calculate functions are required in order to understand what we are doing nonetheless nearly the end of the video i will show you that the code we wrote actually contains a small glitch and fixing that small glitch requires a lot more knowledge it's just a single line that needs to be changed but you need to know that and to know that you need theory so the goal of the video is to show you how to write complex tax code how to combine together simple functions how to understand the basic theory and hopefully that will raise your curiosity to learn more about daxa if that is the case well guess what we do have trainings we have the introducing dax training which is free and you can find it at sqlbi.com and if you want to go further you can learn with the mastering tax training that will actually transforms you into a guru nonetheless let's start by reasoning on top of the code that we need to write as we said we need to compute the number of days which are required to deliver an order and if we look at the orders table we have these two columns we have the order date and the delivery data the number of days which are required in order to deliver an order is just the difference between the delivery date and the order data index you can compute the difference between two days by just subtracting one day from the other so we can create a calculated column that subtracts from the delivery date the order date and that produces the number of days required to deliver the order so we can do that by simply creating in the sales table a new column we call it delivery days first the name of the column and then the expression we want to compute which is the delivery date minus the order date as i said the subtraction of two columns produces the number of days unfortunately the result is not really perfect you see that delivery days shows a data it's not a number and the reason is one date minus another date produces a data if we want to transform that into a number we need to cast it using the in function if i transform that into end of says delivery date minus says order date now this produces the number that i want to see three days four days one days two days and so on right now this is a column and a column can be used directly in a report in order to show its value its aggregate let's do that now that we have the delivery date we can go here in the country in the report that is slicing by customer country it says we have the delivery days i just drop it here and you see it shows delivery dates but the number looks wrong it's not actually wrong it's the sum of delivery days because by default the engine will aggregate a column in numeric column by using sum we are not interested in the sum we want maybe the average of delivery days because that makes a lot more sense you can do that through the user interface so i can go here under delivery dates and instead of showing the sum i can use the average so i just click on average and this will now show me the average of the delivery days now before moving further one important detail you see that we created a column that can contain two one four whatever number of days required but when we project the column in a matrix it always shows the aggregate value it does not show the value of the column it can be the sum by default it can be the average or whatever you might want but the value shown in the matrix are always aggregate values they are not the columns that you actually computed the columns that calculated columns are stored in the model and then used to build the aggregate let's focus a bit more about the details of the number this number 1.74 is a bit too small i know the model and i know that we are not that good we are not able to deliver in 1.70 days on average our orders the reason is as i said earlier we have physical stores and we have the online store and the difference that the delivery days makes sense only for the online store for physical stores this value will always be zero but right now if i just compute the average i average also all the orders with zero delivery days combining them together right now i'm slicing my customer country but in order to understand it better instead of using the customer country we can use the store country on the rows and you see that for all the countries it shows zero now this is the store country except for the online store where it shows 2.95 which is a much more meaningful number i want my measure to compute 2.95 i do not want 1.70 because 1.70 is just wrong so what i could do is add a filter to the report that shows only the sales for the online store the problem of adding the filter to the report is that you always need to remember to add the filter and if a user just places the measure or places the calculation in a report without remembering about the filter for online store the numbers will be wrong that is why the calculated column was a good option but it would be better if we build a calculation that automatically includes the filter so the user will just drag the measure in the report and the number will always be correct to do that we cannot just use a calculated column we need to write a measure a measure is an aggregation a calculation that works at the aggregate level it does not work row by row as calculated columns do and it is able to we will be able in the measure to add filters and modify the logic of the calculation so let's start writing the measure and we started by taking the code of delivery days which is here let me just copy the code and we build a new measure that already aggregates the average so we call it average delivery days now if i write the same expression that i was using before for the column you see that i have an or an error the error says i cannot find the name delivery data both for delivery date and for order date so i have two errors with just a simple expression what is the reason for that in order to understand it we need to introduce the concept of the row context so let me get rid of this measure for now and let me explain how a calculated column works when you write an expression like this says delivery date minus says order data in the in a calculated column what the engine does is it computes the column row by row so it starts on the first row it computes the value then it goes on the second row it computes the value and it stores those values in the table but the relevant point is that the calculation happens row by row technically we say that there is a row context the engine knows in which row it is while computing the values therefore if we look at the data here because our expression is using order date and delivery data the engine will start on the first row here it grabs the value of the order data the delivery data subtract one from the other computes the result and store it in other words the engine always knows in which row it is while computing the values that is the presence of a row context in a measure things are different a measure is not computed row by row a measure is computed at the aggregate level where there are a lot of rows that needs to be aggregated together therefore the expression says delivery date does not have a value the engine does not know which row to use to compute the value of the delivery date column in a measure you always need to use either aggregations or you need to introduce a row context by starting manually an iteration let me show you this so we have the code the measure those we have the column we can go back and write the measure now knowing that it will not work with just a simple code so again right click new measure average delivery days as we said we cannot write sales delivery date minus sales order date because the engine does not know which row to use to provide the value for delivery date and order date but we can introduce a row context by starting an iteration so instead of just writing the difference this way we can do another projects of the expression over sales of the expression that we wrote earlier now average x introduces an iteration the engine because of average x will start scanning the sales table and compute the expression that we provide as the second argument of average x row by row therefore here we do have a row context so this expression is computed inside a raw context and now you see we no longer have any error i can hit enter format it as a decimal number with two decimal places place it here and you see that now my average delivery days computes the same value as the average delivery days so i just rewrote the code as a measure we still need to add the filtering part but while we are here because we created the average delivery days measure we actually no longer need the average of delivery days calculated column keep in mind that calculated columns are good and you can use them in your data model but they increase the size of the model every calculated column uses space in your model therefore even though for newbies it's good to use calculated columns when you learn more dax you also learn that using measures is a best practice so try to build all of your calculations with measures instead of using calculated column because that produces a smaller model and several times also a faster one so let's get rid of the calculated column that is here delivery days we just remove it from not rename but remove from the model okay and now we have our average delivery days as i said we still need to introduce the filtering now averagex introduced an iteration and it is iterating over sales let's go to our calculation it's iterating over sales so it's scanning the entire sales table we want to remove from the sales table the orders which are not happening online and information about where the order happened is in the store table there is a function that we can use here which is filter we filter sales and here i would like to write where store name equals online store but again i have a problem you see that it says that it cannot find the store it cannot find the name name even though in store we do have the name but it doesn't find it why that the reason is a filter is an iterator again it is creating a raw context over the sales table sales does not contain the store name the row context the iteration is happening on the sales table store is a different table and we do not have a row context there therefore if we want to access the store of the current sales we can not just write a store name we need to use the related function related starts from the current row context and lets you access columns in tables that have a relationship with the current row in the current row context it is enough to go here and say filter sales were related store name equals online store and now you see the problem goes away i can now hit enter and you see that the average delivery days appears only for the online store now right now i'm slicing by the store name remember that we were slicing by country by customer country so let's get rid of the store country and we placed instead the customer country on the rosa now this number average of delivery days is the correct value only considering the sales that happened online using filter is good and the filter is a function that filters and iterators they are functions that are very frequently used at the beginning but what we can do is compute the same value not using the row context and the related function but instead relying on the filter context let me explain first what the filter context is that is the second important topic about dax whenever you write a calculation the calculation always happens inside a filter this filter is created typically by power bi by the report by the client tool that you use to build your calculation let me show you that with the code here my measure average delivery days iterates over the sales table but you see that depending on what country i placed in the report i have different values 291 299 289 289. the reason why i have different values is because the calculation happens on different subsets of the data even though my code does not say that it's working on a subject of the data still the filter created by the report filters the entire calculation so when i write filter over sales this sales reference does not reference all the rows in sales but only the rows that are visible given the filter that the report has placed so for germany this will be the germany sales for italy they will be the italian sales for united kingdom that will be the sales in uk the filter context is a filter that is created by the report and it affects all the calculations now power bi through a matrix or through a report can create the filter context so the context under which your code is being computed but you also have the option of creating the filter context by yourself using the calculate function calculate lets you change the filter context by introducing new filters so instead of writing averagex over filter sales what i could do is get rid of this filter iterate over sales take this code and embed everything inside calculator what calculate will do it will create a filter context where only online sales are visible i just copy the code of the filter and one good thing about calculate is that you no longer need related because calculate works with the filter context not with the row context so i can just write store name equal online store and that's it now my average x iterates over sales but this is a reference to sales that happens in a filter context that contains a filter for online store therefore writing the code this way produces the very same number it is a better practice whenever you can use calculate it's quite always better to use calculate because filter removes rows from a table so it needs to scan a table and get rid of rosa whereas calculate applies the filters first it's not actually correct i mean the optimizer does a great job in making both filter and calculate work at a decent speed but with that said whenever you can use calculate to write your code it is actually a bit better now we have our calculation that is showing a good number the average delivery case the thing is this number is still wrong it's not correct even though it's closer to the correct number it is not indeed the correct value why that well the reason is i'm iterating over sales so my calculation is averaging row by row over the sales table but the sales table does not contain one row per order it contains one row per order line a single order will have a lot of lines and my average is averaging individual lines let me show you this with the data if i go to the data view and i sort it by ascending by order number you see that the first order 269 500 contains several lines one two three four five lines and for five times i have the order date and the delivery date now x will iterate line by line and compute the difference between order date and deliver date for each individual line therefore an order with a lot of lines will have a much larger weight than an order with just one line because i'm averaging individual lines it would be way better if i were able to compute the average at the order level the thing is i cannot because my model does not contain data at the order granularity it contains data at the order line granularity what i can do is instead of iterating over sales iterate over another table that has the correct granularity that is the order i could create a table this way a physical table in the model at the order level only for the sake of this calculation or what i can do and it would be better is aggregate the values just before iterating over them so create a sort of temporary table that contains the order number and the columns that are needed to do my calculation we do have a function index that performs grouping and it is summarized summarize can scan a table and group it by a set of columns that you provide so i will not need to create a physical table instead i will just build a temporary table on the fly iterate on top of it and compute my values let's do that together instead of iterating over sales i will iterate over a grouped version of sales and i use summarize i can summarize sales by sales order number now summarize returns all the distinct values of the order number in the sales table but you see i still have a problem if i iterate over summary sales by order number i can no longer access to delivery date and order date why that again is the row context now that i'm iterating over a table that is grouped to contain only the order number i no longer have access to either the delivery date or the order date what i need to do is group by not only the order number but by order number delivery date and order date so that the resulting table contains three columns and i can do calculations on top of that therefore what i need to do is summarize by sales order number sales order date and sales delivery date if i summarize over the three columns now you see that intellisense does no longer provide an error because the table returned by summarize contains the order number the order date and the delivery date if i hit enter you see that the numbers below changed and now they have a different value now this technique grouping works as long as it is guaranteed that each order has only one order date and one delivery date if it happens for whatever reason that for the same order number i have different order dates or different delivery dates then my aggregation would be wrong and i would need to work a lot more we can check that we can look at the result of summarize and check whether there is only one row per order meaning that the combinations of order date delivery date and order number is unique so we can just take the code of summarize and create a calculated table let me copy this code we go here to look at the result and we create a new table it's just a temporary table for the sake of testing so test equals my code beside this is also a useful technique whenever you want to check results in a very quick way and you see that the result is a table that contains the order number the order date and the delivery date exactly what i was expecting but how many rows are there if we look at the number we have here we have 5819 rows so this is number of rows in the resulting table of summarize how many values do we have in the order number again we can check that by looking at sales just click on order number and we have 508 okay here it is ivan 5819 decent values this means that the result of summarize has the same number of rows as the number of orders we have in the model therefore our calculation is safe each order has only one order date and one delivery date now that we check that we can get rid of the test table that was useful only to compute that number so let's get rid of it now we have our calculation average delivery dates that computes the correct value therefore the first step is completed we are now able to compute the number of delivery days and slice it by country but you need to remember that that was not our origin our final goal let me show you again the final result what we want to compute is the number the percentage of orders delivered in one day two days three days and so on so we need to be able to slice by the number of days and then compute this percentage we can start working on this for example by looking at the distribution of orders of the number of orders slicing it by the number of delivery days so what i could do is create a new let me do that with on a new page instead of slicing by country i might want to slice by the number of delivery days and now i'm a bit in trouble because we deleted the column that contains the number of days to deliver an order the thing is if you want to compute some value some aggregated value then measures are good but if you want to slice or dice or filter by values then measures are not the best tool you cannot use a measure and place it on the rows or on the column of a pivot table you need a column to do that so we need to reintroduce our delivery days column that's not hard we just create a new column it was the library days which we know is the integer part of a delivery date minus order date now that we have it as a column i can remove the country place the number of delivery days on the rosa and now i have a delivery days and average delivery days which is not the number that i want what i want is the number of orders delivered in one day to compute the number of orders i can compute the distance count of the order number i cannot just use account rows of sales because that will compute the number of transactions i need to compute the distance count of the order number and we also need to remember that i always need to restrict the calculation to online sales so two sales that happen in the online store so we can create our measure let's call it number of orders it will be a distinct kind of the sales order number and i need to add the filter for the store so we add calculator this thing count well the store name equal online store and now that i have my number of orders i can place it in the report and it shows the number of orders that happen in different that happen with different delivery dates i can get rid of average delivery days and for example shows that as a column chart that shows that the majority of our orders are sold are shipped in two days then in three days a bit less four days again a bit less than one day and so on so we have all the distant all the different values with a distribution we needed a calculated column because we wanted to slice by the column but if you remember what we wanted we do not want to have just the number of orders we want the percentage of orders uh against the percentage of orders delivered in one period against the total number of orders so let's change our calculation we keep a number of orders let me go back to a matrix so we can see the numbers i have the number of orders i need the total number of orders and then i need to divide one by the other so number of orders computes the value keeping the filter for delivery days and i want to have the total number of orders so we can create a new measure which is similar to this one therefore i copy the code of this measure and i build a new one that i call number of all orders that computes the distance count of sales order number where the store name is online store and i need to get rid of the filter from the delivery days so i need to do a remove filters of the delivery days this computes the grand total of all the orders that is three three five one i can place this measure here and it shows 3 3 5 1 everywhere now it's a matter of dividing the number of orders by the number of all orders so i can create a third measure that me computes the number not the percentage of orders which just divide the number of orders by the number of all orders and this needs to be formatted as a percentage i can get rid of number of all orders take my percentage place it here and now this shows that in one day i sell 14.95 of the orders in two days 26.69 in three days 25.99 25.90 so i have created the measure that computes the percentage of all orders that are shipped in one day two day three days and so on i need to do a step further because what i want to have is not the number of orders sliced in this way by the delivery date i want to slice it by country so now that i have my measure percentage of orders i can go back here take my percentage where is it and place it here but that shows always a hundred percent because i'm no longer slicing by the number of orders and remember if we look at the final repo that we have we want to have the percentage of order shipped in one day one and two days one and three days one and from one to five days so that needs to be a cumulative percentage in order to compute the cumulative percentage i need to apply a filter to the filter context that says well the percent the number of delivery dates needs to be less or equal the boundary that i defined so i can again use calculate to create another measure that computes for example the number of orders that have been shipped in at most three days or in at most seven days by using again calculate so what we can do is create a new measure let's call it percentage seven days that uses calculate the percentage of orders and we apply a filter to the delivery days delivery days less or equal i don't remember the number seven this computes the percentage of orders that have been shipped in at most seven days we can place that in the report format it as a percentage and you see that it shows a 99.59 99.23 this is the percentage of order delivered in at most seven days and if i want three days i can do a similar calculation go here build a new measure where i just copied the code and i do the percentage for example for three days now my percentage three days computes formatted the proper way the percentage of three days i can also sort them the right way get rid of this percentage and now i my report starts to have a slightly better shape because i do have the country the customer country the average delivery days that is no longer useful so we can get rid of it and i have the percentage over three days the percentage over seven days but let's go back to the result that we want to obtain you see that we have by country the number of days is on the column and this number is uh unequally distributed 1 2 3 5 7 10 15. whereas if i look at the report i have so far i have measured here percentage for 3 days for 7 days it is true you could create one measure for each number that you want to show in the reporter but that is not really elegant it would be better if we created a table that contains the number of days that we want to add on the columns and then we use that information in order to drive the calculation of our measure so our measure will not use three five seven as a fixed number that drives uh the maximum number of delivery days but it will derive this information from another table that we create in the model so what we need is a table that contains our numbers 1 2 3 5 7 9 10 and so on the best way or the easiest way to create such a table would be that of using a parameter table you can create a parameter table using power bi and that generates a table and builds some measures for you but the drawback is that the parameter table does not give you the freedom of choosing whatever sequence of numbers you want let me show you that you can go here in modeling create a new parameter you can provide the name the data type but then you have to provide the minimum the maximum and the step if i want to create one two five then i cannot because a parameter will be one two three four five with a step of 1 but we have the axon so we can create the table on the fly by just building a custom table by including numbers in curly braces so instead of creating that with a parameter i build the table as a calculated table so we go for new table let's call this table js and i can use curly braces the numbers that i want are 1 2 3 5 7 10 and 15. this green this generates a table with the numbers that i provided let me look at the result here in the data view and you see that we have our table containing all the different values 1 2 3 5 7 10 15. the column is named value which is not really a cool name i want to rename the column i could do it from here by just choosing the rename feature or i can use select columns because it is true i could use rename but i mean we are learning dax and there is a specific dax function that you can use to rename columns in a table or to reduce the number of columns in the table and this function is select columns you use select columns you pass to it a table and then you choose the columns that you want out of the original table plus you have the option of renaming them so we can simply go here and we use select columns out of this table we want a new column that we call days and its expression is value let me get rid of that so select columns out of this table i take the column name value that is the column name that has been chosen by default by the constant table and i rename it as days the result of this table is now a table that contains one column days with the values that i want now what i can do is place this column on the columns of my matrix and then use the information because if i just place the column in on the columns of the matrix let me get rid of percentage seven days so we have only one value and from the days i grab number of days and place that on the column you see that that shows the same number everywhere because my code is not using the information stored in the days table it's just using the fixed value 7 3 or whatever was the number in that measure the next step is to grab that value and use it in my code so i have my percentage three days let me copy the code i can create a new measure and the new measure will be percentage x days where x is whatever value we place on the columns instead of three i need to use the value that is present on the columns and here i have another problem i cannot write here days days the reason is always the same we do not have a row context that provides a value to the days column in the days table we should have an iteration in order to have a raw context and here for this code an iteration does not make a lot of sense nonetheless there is a function that lets you grab the currently selected value in a table if the table has only one value visible in the current filter context this function is named selected value and lets you grab the individual value present in the filter context if there is only one so what i can do is create a variable let's call it x days where i use a selected value select the value of these days then we store in another variable our calculation return result i'm missing a parenthesis here and then now now that i have x days in my variable i can use it here instead of the fixed value 3. now this code is going to compute the percentage taking the value for the upper boundary from the columns or for whatever i used in order to filter only the given number of days that needs to be formatted as a percentage then we removed percentage three days and we place percentage x days now you see this is way better let me find some space so we see both the code and the result at the same time now this is telling me that i have 16.53 percent of orders okay shipped in one day 39.60 in two days or one or two days and so on so now my percentage over x days is computing the number that i want the cumulative percentage of order shipped in at most the given number of days where i can configure the number of days so the values that appear on the column if i'm satisfied with those numbers i'm happy if i want to change them it's enough to change the content of the table that i placed on the column the days table and that will drive the calculation the right way still i have a couple of issues that i need to solve first of all you see that i'm showing a hundred percent here and i'm showing a hundred percent here if you remember what we had in the result let's take a look at that we do not want to see a hundred percent everywhere we want to see a hundred percent only when we reach a hundred percent for the first time then we want to blank out the measure we can add further logic for example i can go here and say well do not return the result if it is above a hundred percent or if it is a hundred percent if i do that i can check if result greater or equal that one remember that one means a hundred percent uh actually if the result is less than one then return the result otherwise return a blank the thing is if i hit enter now you see that all the values of a hundred percent disappear and that is not what we want to obtain we do not want to remove a hundred percent everywhere which is what this code is doing what we want to do is slightly different we want to show a hundred percent the first time and never again for the larger values therefore what we can do is check whether a hundred percent was already reached before if i'm showing five days i can ask well check if in four days we reached a hundred percent and if that is the case do not show the value because we already shown that for four so i need to check if in the previous value we reached a hundred percent we can do that we have our result we can create a new variable let's call it check or result let's call it previous result where i compute this calculation but i use x days minus 1 the previous value and then i do not check result but i check if the previous result is less than one then show the result but if it happens that the previous result is a hundred percent or above if that were possible then hide the value if i hit enter now you see that something good is starting to happen even though we are not still perfect because we see a hundred percent here but we do not see it i need a bit more real estate okay we do not see it here we only see there for seven and that's the only 100 that we see everywhere now why is that the code now it's time to look at the code a bit better checks x days with x days -1 meaning that here in the column seven it will check it against six the column with ten will check it against nine but maybe a hundred percent was not reached in nine it was reached before or it was reached at a different point in time we cannot check with the previous value otherwise we do not show 10 because we reach it at 9 but we are not showing 9 anywhere therefore when we are showing the column for 10 we need to check it against 7 because my table contains a jump from 7 to 10. let me show you that by enlarging the table because it helps in understanding the problem let's go on the days table and we add all the gaps between seven and ten eight nine okay 8 9 10 15. if i now hit enter you'll see that i show a hundred percent of on eight i do not show it for nine and ten the problem is when my code makes the calculation for 10 it will not show the value because it has already been reached at 9 and it has already been reached at 8 but the thing is i'm not showing 8 i'm just showing 7 and then 10. so what i need to do is make sure that when i'm computing the value for 10 i check if at 7 i already shown 100 i ignore eight and nine and if i didn't show the value for seven i show a hundred percent for ten that requires a bit more work let's get rid of eight and ten so we go back to the problem the dashboard that shows the error and then we fix it my previous result is using x days minus one we cannot use x minus one we need to use the previous value that was present on the columns and how do i compute that well when i'm here in the column for 10 the previous value is 7. and how can i compute 7 7 is the maximum values in the days table that happens to be strictly less than the current value so for 10 the maximum value before 10 will be 7. that way i can compute the previous visible value that is a different value than just the previous value therefore i need another variable let's go on the code and i can i compute the previous value i use calculate well let's start with max i take the maximum of four days days and then i need to restrict this calculation to say i want the maximum days days where days days is strictly less than x days now my previous value actually i can compute previous x days and just to be consistent with the name so this is now the previous value that is 7 in the case of 10 and i use a previous x days here instead of x days minus 1. by doing that now with this code you see that the numbers are computed correctly i show 99.59 for seven and then i jump on a hundred percent to ten i don't know when between seven and ten that value has been reached the value 100 but depending on how i build my table they place on the columns i will obtain the correct result and we blanked out all the values after the i reach 100 for the first time so you have seen so far we have been able to create the entire calculation by combining different pieces of dax code still if we look at the final dashboard the final dashboard contains nearly the same values but it is also colored so we need to go for the next step that use that is color the right way my my dashboard so i need to understand when my percentage is above or below the overall percentage therefore i need to compute the percentage for the current selection and the percentage for any for overall time and then i compare the two and i will build a measure that returns one if i want the cell to be green or blue and -1 if i want the cell to be red and i will use this measure in the conditional formatting feature of power pl power bi in order to color the background of my cells the right way therefore i need the measure that returns -1 or plus 1 depending on the relationship between the value of the percentage for the current selection and the value of the percentage over all all time all time all products or countries and so on so i can create another measure let's call it previews no it's percentage of uh pct percentage xdays color i need first the current value so i store it in a variable current value which is a percentage over x days then i need the total value the value of all orders let's call it total value how do i compute the total value the total value is here 14.94 percent in order to compute this value what i need to do is get rid of the filter that happens to be on the country so i have a filter on country if i get rid of that i will obtain my grand total but i can do more than that instead of just removing the filter from the country i get rid of any filter so that i always obtain the grand total and that produces the number that i want so i can compute total value by using calculate calculate its percentage over x days and i use remove filters sales that removes any filter from the size table and all the tables and have a relationship with sales then i compare one with the other and i say result equal if carbon value is less than the total value that means i'm below the average and i want minus one otherwise i return plus one actually that is not needed return result let's check that this actually computes a value let's say that germany is below 11 for 11 49 is less than 14 so i want germany to be minus one and i want canada to be plus one we can check that by adding percentage over x this color to the matrix and you see that we end up having germany with a minus one that is good and canada with a plus one again that is good our measure is computing the correct value now i can remove it from here i don't want the measure actually i want to use it to format the background so i we do conditional formatting of the background color we want to use a set of rules that is based on percentage over x day's color if the value is equal one as a number then we use this color because a plus one means good and then if the number is equal minus 1 that means bad and we call rita with something that resembles red and clicking on ok we have our result now this is the dashboard that we created right now and this other one is the dashboard that we had as a goal you see that we obtain the same result that the numbers are correct it is computing uh the percentage and everything is working the right way so as you have seen what we did was combining different functions different simple functions in a single calculation in order to obtain quite a complex dashboard there were no dax function able to compute the percentage of orders over time or the percentage of orders with a given number of delivery days but by combining dax expression we have been able to build some quite a complex calculation still as i anticipated at the beginning this code has a small glitch and understanding how to fix the glitch requires a bit more knowledge that i have been able to provide you in such a short video because you need to understand a lot more about the filter context and how the filter context is changed by the calculate function let me show you first where the problem is then we will see how to solve that small problem and finally we'll draw the real conclusion of the video let's say that i'm happy this model this report works fine and my users start to use it for example a user might start using it and add a slicer a slicer for example over the product brand i use it as a slicer okay maybe use a larger font here on the values we use let's say 15 points we can also exaggerate and go for 20. so when the user clicks on adatum you see that the numbers change only taking into account orders that contain a datum the thing is look at north wind traders if i look at north wind traders germany and italy they are both colored in red even though the total is 971 percent so they are both above the total but still my measure is coloring them the wrong way therefore there is a glitch somewhere in my code i'm not computing the values correctly and i need to fix that i need to find where the problem is and it turns out that solving the problem is actually extremely simple but you need to know where to search and how to understand what the problem is specifically in my percentage over x day this is the calculation that i computed known the percentage x stays color okay what i need to do is fix the problem and to fix the problem i need to get rid of this remove filter sales remove filters specifically is removing any filter from the sales depending on what i want to obtain i need to change that remove filter to a different version i might need to use a all acceptor i might you need to use remove filters and values i might need to use all selected which in this very special case is likely to be the best option so look what happens if i just replace remove filters with all selected that simple change will fix the problem of germany and italy and make the entire coloring right i just hit enter and now you see that the coloring happens the right way as it often happens with dax small changes they actually change a lot about how the code is being computed now through examples i cannot explain the details of what oscillator does or all the details about calculator or make you understand the theory the on the entire point of the video was to show you first that you do not need to know a lot of dax functions to build gorgeous calculations you need to know how to use the basic dax function and combine them together into a single calculation combining functions is much more powerful than just using predefined functions then of course you can use predefined functions if you have them but if not then you have the power of building your own calculation by yourself but then in order to combine those functions what you need are two ingredients first of all you need to understand theory because every small change to your formula might have a strong impact on the final result and you need to understand that otherwise you write code that has glitches as we have seen during the demo moreover you also need practice the skills of combining simple functions in complex calculation comes with practice that is why in the training that i deliver i always teach theory first and practice later some theory and a lot of practice because to me this is the right way of learning dax if you just look at simple examples and you think that you are actually learning ducks i'm sorry but this is not what is happening worse you might have the feeling that you are learning but you're not learning it right you're not following the best path that makes the best usage of your time in learning this new language i do hope that you liked this video and that you appreciated how complex calculation can be actually solved with a small amount of knowledge all what you need to know is the row context the filter context these are the basics and we do have context transition expand the tables a lot more theory but the solid foundation on the raw context and the filter context is already enough to build a lot of calculations and build cool reports that will work all the time if you want to learn them the right way well go to sqlbi.com and start right now attending the introduce index training it is free and it provides you with the foundation needed in order to write calculations like the one we have done so far if you want more then we do have the master index training that will go much deeper and teach you all the details all the theoretical and practical details it also has exercises and all what he needed to become a dark school and after that i hope that you will be with me in saying enjoy dax

Channel: SQLBI

Views: 213,372

Keywords:

Id: f5IdCDF1fj4

Length: 71min 50sec (4310 seconds)

Published: Sat Mar 12 2022