Solving errors in CALCULATE filter arguments

to our friends in this video we're going to see how to solve common mistakes in filter arguments of the calculate function when you write a calculate function in Dax usually you write feature arguments logical conditions that you want to apply to the fitter context to obtain the required result however sometimes you write a condition but you get a narrow message that is not easy to understand so we will try to explain these common error messages and we will see what are the best solutions to obtain the required results let's start with the demo so I prepared a number of demos to show different error messages and for the first one I want to apply a filter over the country where we made sales now we have the country in a customer and store table but let's start featuring just the customer country region just to start to see start to remember what happens when we write a feature condition in calculate so for example I could write customer country region equal to United States and what I get as a result is that the sales amount measure in sales country states that you see in this area of the report is filtered by only including those customers in the United States I can certainly change this condition in including more countries for example I can type Canada and this way I include the customers that are either in United States or Canada and as you see the number is likely larger than before now remember a filter is a table so whenever we write a filter condition what happens is that the Dax engine has to translate this condition into a table expression and a table expression must include the colors that we specified and the con The Columns are retrieved using the all statement including the list of the columns that we specify now because we have only one column we can just include customer country region and remember we don't have to write a code this way and just explaining what happens underneath when we write a single fitter condition with a predicate so my line 6 highlighter now is translated into the filter we see from line four to line seven when I execute the code the result is identical because of course this is just a translation of the extended syntax that is obtained automatically now we could include another filter using two or more columns for example if I include says quantity multiply by net price greater than 1000 if you look at this condition this condition now is referencing two columns of the same table and I can validate this measure this measure is applied and actually Fitters the data for those two columns but remember that underneath filter is a table and in this case we obtain the table filtering all the unique existing values in sales quantity and says quantity oops I made a mistakes says quantity and net price sales net price of course so what happens is that the table that is obtained by the filter from line four to seven now let's include the comma this table is a table that has two columns and only the existing combination in the table says the satisfy my condition says quantity greater than that price so I wanted to show these two examples that do not produce any error to make you aware that whenever we write condition Dax has to write the filter statement and the filter has to use an all statement that has to list the columns specified in the filter now all can only include columns from the same table which means that we might we might go in trouble if we try to include Columns of from different tables which is what I'm going to do for actually creating the measure I wanted to show in this demo and in this demo I want to show a measure that filters the sales made to customer who live in the same country of the store where the purchase was made so we have a country region in both store and customer we want to create a condition that says customers customer country region equal to store country region so let's get rid of these two filters and let's write the filter I want to write so I want to write customer region equal to store count to region now as you see intellisense is not helping me actually is not suggesting me that country region can be used in this filter condition actually it cannot be used and if we take a look at the list of the columns we have in the store table and in a moment we will go there but you see that store has a column called country region but as you see when I click enter what happened is that we got an error and this condition actually is a condition that produces a syntax error and the error says the expression contains columns from multiple tables but only columns from a single table can be used in a true false expression what does it mean what is a true false expression in this error message well the true false expression is the expression we have to write in the predicate remember that this expression with be applied to a filter and the filter must be generated using an all statement that includes all the commas and guess what the issue is that we cannot write a filter that has the two columns I wrote here let's try if I write write sorry filter and I specify these two columns in the all statement with a comma and I close this and I close also the filter here we go what we get now is all column Arguments for all or one of those other all functions must be from the same table so all is a function that only accepts column from the same table and filter all is the actual X is the actual statement produced by your filter condition in calculate so we can simply we simply cannot write a condition this way but yet I still want to create this feature because from a logical point of view I want to say Okay I want to get all the possible combination of of all the possible combination of store country region and customer country region I only want to consider those transactions made to customer that are in the same country of the store I mean from a logical point of view I can do that I just cannot write it the way I wrote it how can I make this happen I can replace I can explicitly write a filter but I have to replace the table to iterate with the table that has the the values from those two columns that produce a row contest that will make this condition valid and what I want to obtain is a table that has only those combination of control regions that exist in both customer and store and it actually can filter existing transactions so the idea is that I can do the following so I have a function called cross join that can create all the possible combination between two tables and so I can get the list of the country regions from the customer table and the list of the country region from the store and combining them together I get all the possible combination out of them I want to filter only those that have that particular condition which is the two names has to be equal so let's write all customer country region and all store country region now at this point the result of the Cross join would be a table that has the two columns I want and this condition will be a valid condition so let's try and here we go now this is a valid condition let's just fix the format and you see that in the visualization of the two measures of course the same country says is a subset is always less than the sales amount because this amount is the system amount for all the customers all the stores for each particular brand but when we reduce the scope to the customer who live in the same country where the store was involved then of course the set is reduced and this is the result that we obtain so actually we solved the first problem which was what to do when the error is that the feeder expression contains columns from multiple tables so columns for multiple tables are not allowed in the syntax that has to be translated in the filter all but if we can create a table that has a columns from different tables we can actually create a filter that combines columns from different tables so this was the the first example of this list of common mistakes for the filter arguments in calculator now let's move to the next example where we want to create a measure called safe stop prices at the moment says the prices is just a measure that doesn't produce any particular result it's just a copy of sales amount what we want to do we want to change this calculus so that we filter the net price so we want to apply a filter over the sales net price and the business rule so let me write this the right way okay and the business rule is that we want this filter to be of those transactions that are that have a net price a price of the transaction that is above 90 percent of the maximum value of the maximum price for all the transactions so if the top price is 1000 we want to filter all those prices that are greater than 900 so 90 percent of the maximum value of all the transactions so how can I do that well I can start typing a calculator so sorry let me let me explain what I want to do I want to get the maximum value of all the transactions and if I write this condition in a single statement I could say I want to calculate the maximum value of the sales and net price sales net price removing any filter remove filters because I want to include all the transactions so this calculation has to be made once and has to be made ignoring any filter contest want to get the maximum value it all the transactions say stable so and of course this is the maximum value I have to multiply this maximum value by 0.9 because we want to get uh the limit the boundary should be nine percent of that maximum value so if I try to execute this code I click enter you see that I have an error so I cannot display the visual because now the error is the following function calculate has been used in a true false expression that is used as a table Theta expression this is not allowed what does it mean so basically the problem is the following even though technically it will be possible to create a fitter statement and this is what we are going to do now that includes a calculator statement what um what Dax does it does not allow you to do that because the presence of calculate involves a context transition and a context transition in this condition will be a context transition over the iterator over just one column which is sales net price which could probably affect the result now in this particular case because of the presence of remove filters I'm going to ignore that context transition but the reality is that Dax tries to avoid to to create the syntax that would be misleading and most of the times you don't want to obtain the context transition for this particular condition probably you want to compute this calculate before the filter itself something that we will do in a moment so let's see what are the possible solutions the first solution is basically to manually write the filter if I write filter all sales net price comma and then I write this condition in the feature argument this feature written manually is a filter that works and I obtained the result that I wanted so you see that in the result we see that the only brands that have a price greater than 90 percent of all the other brands is only present in contoso proseware and wide world importers brands okay so this is not important but just to show that we are actually filtering data so actually this can be this kind of works so as I said it's a it's more a safety feature of Dax not allowing to automatically translate this calculation because the content solution could change the behavior and in any case write in the code this way is also dangerous for another reason what we are saying is please compute this calculation for every value of sales net price so what we are asking if there are 1 000 unique net prices we're asking to compute this value 100 times 1000 times so it's useless this this number is not going to change the fact that we've wrote remove filters means that we want to include any filter so the best practice when you have a calculate involved in the um in the filter argument the predicate of calculate is much better to prepare a variable so we call this variable for example um filter price or price limit price limit is a better name price limit equals two and I just paste my code and then I write in result the result of the calculator where I use price limit here at this point there is no reason to keep the filter I can generate the feeder automatically I want to keep some condition that is relatively easy to understand and I return the result here we go so if I write the code this way I obtain the same result I didn't change much before but compared to the example I shown you before but now I made it explicit that the calculation of the price limit is made before the calculate is made in the feeder context before the calculate is made using the external filter context but most important is made only once so I don't need to compute this calculation every time for every single value of saves net price this can be computed uh once for all the all the filters that are applied here so this is another important example so every time we have a contest transition we have potentially this error message so the error message that you have seen now appear because we use the calculator but actually it would appear also if you use calculate table or any other function that performs a context transition for example last date last date is a function that performs a Content transition and if you think about the cumulative sales I have another example I prepared here so the goal now is to create a measure cumulative sales that sums the sales amount for any date less than or equal to the last date visible in the report which means that for 2018 we want to see the sum of 2017 and 2018 for 2019 we want to see the sum from 2017 to 2019 and so on so how can I write cumulative sales so that it actually Returns what they want so I want to write a filter where I say Day date is less than or equal to and I have to write the condition the the value of the last date in the current filter context now what I'm going to write is not correct I know that but I see that this is a something executed not executed but used often so using last day to get the last that the last date available in the filter context without applying this result directly to the feeder context let me explain what I mean with this last date is a table function last date returns a table that has one column and one row and the value of that row is the value of the value that we can obtain using Max of date date now last date Returns the table and last date involves a context transition as you can see from the documentation in the index guide but if I write this code you see that the error message is actually the same a function last date has been used blah blah blah this is not allowed once again the problem is not that last date is a table function the problem is that last date performs a contestant residual every time you try to use a function that performs a context transition in a predicating calculate you will get this message and of course you will just see that instead of last date you will see the name of the function you're trying to use solution just use max Max is a scalar function that actually Returns the value that you need now in this particular example so let's just check that the numbers are correct you see that now the value for 2018 is the value oh here we go the value for 2018 commodity of sales is the sum of these two numbers and the value of 2020 is the sum of all the years that we see here so it's actually working and the only thing I don't like I don't like to keep an aggregation in the predicate I mean the syntax now allow that this is this was not allowed until 2020 if I remember well but the idea is that once again remember that what is happening under the cover is this so filter all day date and then we have our predicate here because of that what we're asking please compute this maximum value for each date we have in the date table which is not necessary I mean we can compute this value once for and for all before and even though daxa could optimize the code even though I used the max in the predicate it's more readable and more clear my intention that I want to compute this value only once so last visible date is equal to the value the max of date date then I return last visible date here once again the syntax I really want to write is the shorter syntax I use the the filter just for explaining what was happening under the cover but this is the best practice I prefer to use so I prefer to filter using a limit last visible date that I Define a variable declared before the calculate this way it's easier to understand that this maximum is executed before calculate in the previous filter context only once and this is another best practice for your measures now another example I want to show is what happens if we use a parameter so in this in this page we created this parameter using the feature modeling new parameter numeric range once I Define a numeric range between 0 and 500 if I remember well what do you obtain is a table that you can see here Max price and this is the table that we used to define the slicer so the slicer has actually a connection to the max price column of the max price table and as you see there is a measure Max price value which Returns the value selected here so whenever you move the slicer the the value changes and the number that we see here let's return to 500 the number we see here can be consumed in your Dax code by simply referencing this measure that internally calls a selected value for the column that we are using so this is the situation we need to use max price value as a major reference in our calculation where we want to define the where we want to consume we want to use the selected value in this slicer so the goal now is to change the measure says below Max price so that we filter the sales amount only for the net Price Less Than the maximum price we defined in the slicer so if I ate it so let's find the definition of this measure now this is just a calculate what I try to do at this point I try to write sales net price I have to write a comma to avoid the error message say its net price is less than Max price value so you see that this time Intellis is actually suggested me to write the measure this is different from what we experienced in the first demo when I try to use a column reference from another table so actually the measure was proposed by intellisense but when I click enter when I confirm this measure I get this other error and this area is funny because this area is similar to the error that we have seen before for calculate and last date but now it says a placeholder a function placeholder has been used in true false what placeholder is so um this is a uh hard to explain because we don't have a function a Dax function name placeholder what is happening is that we are invoking a measure reference here the major reference is this Max price value whenever you have a measure reference what is happening is that this expression is replaced by calculate and in inside calculate we have the expression of the measure reference calculating both the context transition this is the reason why we have the error so we might expect to see a function calculate has been used even though you don't see the calculator the calculator function is automatically generated by the measure reference but the idea is that we don't have I mean Dax should have used a different error message in my opinion but what is uh happening is that we have the generic error for all the functions that have a context transition in this case we don't have an explicit function because we are using the measure reference the problem is always the same the problem is that this expression is not going to change for each unique value of sales net price is the same for the entire report so what I'm gonna do I'm gonna create a variable example Max price equal to the measure reference so let's copy this here and then I return the value of sales net price less than Max price this again solves the problem so as you see it's the same problem we have seen before and let's fix the format you see that now when I change the argument the the parameter Max price the number of immediately changes so I can actually use in sales below Max price measure the measure reference Max price value I simply cannot use a measure reference in the predicate in the filter argument why because once again we are using a contest transition and the context transition is not allowed in these cases so but what happens when we want to define a measure without specifying a column this is generates a a similar error but in this case the solution will be different so let me explain this with a different requirement the requirement now is very generic we want to show in sales profitable those are sales that have at least 60 percent of margin what does it mean so we have a measure margin percentage that has a particular calculation and divide the margin by sales amount so in other words we know that there is a margin that we could display to see for example for each year what is the margin percentage but when I say I want to see the sales that are that have a margin of at least 60 percent what do I mean I mean the problem is that we are not specifying whether we want to filter the transactions that have that margin the products the customer or the store so we're not specifying what is the target of the filter and Dax has this problem if we don't specify the target of the Theta we will get an error so indeed if I try to write margin percentage which is a measure that exists greater than 0.6 that represent my 60 percent of the limit that I want to specify for this measure if I click enter once again I have the same error a function placeholder has been used now in this case you understand that there is no point in moving margin percentage outside because again the problem is that what do we want to fit actually margin percentage is the target of the feeder we want to filter those entities that have a margin percentage greater than 60 percent so what we have to do we have to specify with a filter what we want to filter for example let's say that we want to filter the customers that have at least 60 the predicate is right in this case we just have to specify in a filter the with an iterator we want to filter for example the customers so we want to filter only those customers that have 60 so if I write this I obtain the sales of profitable customers right because now and let me fix okay I just have I have an example with this measure in the model so let's uh use another name so if I use sales profitable customers one I actually can see that this is the the the the set these are the sales of the profitable customers what if I want to create the same for the stores well I could create a measure sale profitable stores where I simply oops sorry where I simply replace customer with store is the same structure I use a different measure and in this case if I use sales profitable store we see the different definition the different measure with a different filter so in this case we are saying in 2017 the sales of the customer that have at least 60 percent of margin is 1 million but when we look at the stores is a much smaller number because of course a single customer can have a a large margin maybe that there are a few customers with a big budget but when we look at the store the store also has sales for customers that are not so profitable and we see that the sales of Providence stores is much smaller so probably the customer that has this uh profit this profit is uh maybe buying from different stores or that store also has a number of customers that are not so profitable so this is another kind of analysis but I wanted to explain that you cannot just filter by a measure in order to fit it by a measuring calculate you have to write the filter specifying which column or which table in this case is the dimension table we can fit our Dimension table which table or column we want to filter according to the value of the margin computed for each store or for each customer because in that case this is actually what we want to do we want to filter the the an entity where the main measure has been evaluated for each instance of that entity we have seen examples with the stores and with the with the customer in in this demo so we have seen that when we use calculate and we have a strange error messages in our predicate there is a solution the solution sometimes is just to write the feature in an explicit way other times we have to move the condition before calculate other times we have to embed the measure reference in a explicit filter where we specify which is the business name which is the business entity we want to filter so that we obtain the correct result without getting the error message enjoy Dax [Music]

Channel: SQLBI

Views: 16,443

Keywords:

Id: jYaSd565GgE

Length: 30min 54sec (1854 seconds)

Published: Thu Dec 08 2022