Specifying multiple filter conditions in CALCULATE

ciao friends and welcome to a new video about dax in this video i want to show you the latest feature introduced in the dax language in march 2021 indeed in this version of dax microsoft introduced the capability of using multiple column filters in calculator this is a very small and simple addition but it goes in the right direction of enforcing the usage of best practices and making dax simpler so in the video i want to show you how the feature works and while in the process of learning the details we will also review how conditions in calculate are translated into table filters in calculator that helps in understanding the semantics of the new feature and how to take advantage of it the right way let's get started let's start by writing simple code and reviewing how calculate works in this code i created a measure red sales that computes the sales amount of force in the color to be read and then i use summarize column i slice by color again and i compute red sales now if i run this query you see that the result contains the same value of sales amount for all the rows that means that the filter coming from the summarized column function that is blue black green or orange is replaced by my filter that is filtering only red that is the usual work of calculator what if i want to compute a different measure i want to compute red or contoso sales i replace it here and also here and i want to use a different condition not only red but red or brain equals contoso so i use an or and then i say that the product brand need to be equal to condozo if i now run the query it is going to work but if i run the same query a few weeks ago that wouldn't work and we need to understand the reason why let's first check that it works and it computes the same value everywhere so again it's ignoring the filter on the color and it's also ignoring the filter on the brand we can check that by summarizing by product brand run the query and you see that the same value appears again the filter on the brand or on the collar they are both replaced by my new condition now why didn't it work up to a few weeks ago because the filter that you place using condition is actually translated into a table filter and the way this translation happens defines the semantic of your calculation so we go back to red sales we review how the filter is replaced is translated into a table function and then we see how the new feature works again by creating a temporary table that contains the values that we want to filter let's get started so first of all let's go back to our red sales replace that we filter again by color and we use red sales again around the query it produces a result as i said when you write product color equals red the calculate actually translates it into a table filter calculate translates this condition into filter all all product color where product color equals red the previous condition is translated into this condition and the result is going to be exactly the same if i want to use a more complex condition so instead of just color equal red i want to use product color equal rate or product brand equals contoso this query is now not going to work if i hit enter you see that it produces an error and the reason is my filter function is iterating all product color and it cannot uncheck color and brand at the same time so what most people did before this new feature was to instead of filtering all product color filter product if i filter product i'm now iterating over the entire table that means i can access any column of the product table so if i run the query now it produces a number the thing is the number is no longer the same everywhere why that well because i'm filtering products so i'm no longer replacing the filter coming from the sunrise column because a product is working under the filter context created by summarize column if i want to ignore that filter i can do that but i need to use all around product that produces again the correct value because it's a product it's a color or contoso but i'm removing all the filters from the product table that is a bit too much i'm ignoring not only the filter coming from the color and the brand but any filter on the product table moreover this also has another problem that is i'm filtering a table and you know that filtering tables entire tables is quite always a bad thing to do you need to restrict your calculation to the smallest number of columns that needs to be used in order to apply your filter so the right way to write this code is to write filter all product color and all product brand by writing the condition this way now my filter is working on all the colors and all the brands so it's ignoring filter on color and brand and it can apply the condition if i run the query now it produces the same number everywhere filtering only the two columns that i'm interested into and this is exactly what the new feature does if you do not use this larger condition before let's format the code and you just write a condition using multiple columns it is translated into filter with all on both columns so it is going in the direction of enforcing best practices which is always a good thing it does not always work because the columns that you need to use need to be of the same table if i want to place a filter not only on the color but on the date for example let's say i'm interested in color equal read or date calendar year equals calendar year 2010 and if i try to run the query right now this way you see that it doesn't work and it says that the expression contains columns from multiple tables but you can only use column from a single table if you use the new syntax the reason is when it creates the new table with all all accepts only columns coming from the same table you cannot mix a table different tables in the same expression if you need to write a condition that use multiple columns you still need to go back to the original scenario so you use a cross join you need to use a filter over the cross join of all product color let me write it better and all data calendar year and at this point i created a temporary table that contains a color and year and i can make this work if i hit if i run the query now you see i obtain my result filtering only 2010 the behavior of the new feature using multiple columns is by default overriding any filter coming from the outside so if i filter color and brand two columns in the same tape of the same table i will ignore both filters what if i don't want to ignore the filters well if that is the case i need to use the key filters modifiers let's see that i need to go back to the original query so let's use product color equals red or product brand equals contoso okay run the query and you see that i'm ignoring the filter on the color and the filter on the brand because of the all introduced by the code what if i don't want to ignore so i want this query to only show the values when i'm showing the red color if that is the case i can use keep filters keep filter cells calculate not to replace outer filters but to merge the outer filter with the current filter and if i run the query now with keep filters you see that oh because i have a red or contoso i'm not ignoring the filter red now the number is different look at if i don't have key filters i have the same number everywhere because i'm ignoring the filter on the color but if i do have keep filter now the numbers is different because i'm no longer ignoring the filter on the color so these are the sales which are red this is white or red so the red part is ignored and contoso is the only filter that is being applied further as you have seen it's a very simple addition to the dax language the important thing is that it goes in the right direction of enforcing the usage of best practices it filters the smallest number of columns that are needed to satisfy the condition so all is used by default therefore it ignores filter coming from the outside if you want to keep the outer filters you just use keep filter in order to keep the outer filter and merge the new filter with the previous one a small addition that makes dax simpler enjoy dax

Channel: SQLBI

Views: 47,454

Keywords:

Id: jyxZxFT_pAY

Length: 11min 38sec (698 seconds)

Published: Tue Mar 30 2021