{"id":83924,"date":"2019-04-17T19:27:36","date_gmt":"2019-04-17T19:27:36","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=83924"},"modified":"2025-06-27T14:04:40","modified_gmt":"2025-06-27T14:04:40","slug":"using-the-filter-function-in-dax","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/bi-sql-server\/using-the-filter-function-in-dax\/","title":{"rendered":"Using the FILTER Function in DAX"},"content":{"rendered":"

The series so far:<\/strong><\/p>\n

    \n
  1. Creating Calculated Columns Using DAX<\/a><\/li>\n
  2. Creating Measures Using DAX<\/a><\/li>\n
  3. Using the DAX Calculate and Values Functions<\/a><\/li>\n
  4. Using the FILTER Function in DAX<\/a><\/li>\n
  5. Cracking DAX\u00a0\u2013 the EARLIER and RANKX Functions<\/a><\/li>\n
  6. Using Calendars and Dates in Power BI<\/a><\/li>\n
  7. Creating Time-Intelligence Functions in DAX<\/span><\/a><\/li>\n<\/ol>\n\n

    The FILTER<\/code> function works quite differently than the CALCULATE<\/code> function explained in the previous article. It returns a table of the filtered rows, and sometimes it is the better approach to take.<\/p>\n

    I\u2019ll spend most of this article explaining how to create the following measures:<\/p>\n

    <\/p>\n

    The columns above show, respectively:<\/p>\n

      \n
    1. The city name;<\/li>\n
    2. Total sales for the city in question (the filter context);<\/li>\n
    3. Total sales for the city in question for 2018;<\/li>\n
    4. Total sales for the city in question for the USA;<\/li>\n
    5. Total sales for the city in question for the USA for 2018; and<\/li>\n
    6. The percentage each city\u2019s sales contributes to the total.<\/li>\n<\/ol>\n

      First, I\u2019ll show you how to set up the example. Then I\u2019ll dive into the syntax of the FILTER<\/code> function. I\u2019ll finish by highlighting the differences between the FILTER<\/code> and CALCULATE<\/code> functions.<\/p>\n

      A Quick Refresher<\/h2>\n

      To work through the examples in this article, you\u2019ll need to create a simple Power BI report containing a single table and then create and show a series of measures. Here\u2019s a quick run-through of how to get started.<\/p>\n

      First, create a Power BI report based on the tables used in the previous articles. You can load them either from the SQL Server database given<\/a> or the Excel workbook<\/a>. You should now have something like this (if your diagram looks a bit different, you may not have updated your instance of Power BI to include the March 2019 update, which included the new Model View):<\/p>\n

      <\/p>\n

      Now create a table in Report<\/em> view to list out the city names. Make sure the Table<\/em> visualization is selected and click CityName<\/em> in the Fields<\/em> list.<\/p>\n

      <\/p>\n

      Switch to the Home<\/em> ribbon and select Enter<\/em> Data<\/em>. This will add a new table to your report to contain your measures. To understand why you might want to do this, see this previous article<\/a> in this series:<\/p>\n

      <\/p>\n

      Give this table a name. Here I\u2019ve called mine All measures<\/em>:<\/p>\n

      <\/p>\n

      Click Load<\/em>. Now add the following measure to your All measures<\/em> table. You can right-click on the table and choose New measure<\/em> to do this:<\/p>\n

      Sales = SUMX(\n    \/\/ multiply the price of each transaction by\n    \/\/ its quantity, and sum the result\n    Sales,\n    [Price]*[Quantity]\n)<\/pre>\n
      Choose to display this measure in your table:<\/p>\n
      <\/p>\n
      You should now be able to see the total value of sales for each city:<\/p>\n
      <\/p>\n
      What happens if you want to show the sales for American cities only? Or sales taking place in 2018 only?<\/p>\n
      The FILTER Function<\/h2>\n
      The measure for the sales column shown above, giving total sales for each city, is as follows:<\/p>\n
      Sales = SUMX(\n    Sales,\n    [Price]*[Quantity]\n)<\/pre>\n
      What this does (as readers of this series of articles will know) is to iterate down the rows in the Sales<\/em> table, calculating the price multiplied by the quantity for each and summing the result for each city to get this:<\/p>\n
      <\/p>\n
      To get the sales in 2008, you could use a CALCULATE<\/code> function so that this measure would work:<\/p>\n
      2018 sales using CALCULATE = CALCULATE(\n    SUMX(\n        Sales,\n        [Price]*[Quantity]\n    ),\n    YEAR(Sales[SalesDate]) = 2018\n)<\/pre>\n
      This takes the filter context for each city, and further reduces it to consider only those rows where the sales occurred in 2018 to get this:<\/p>\n
      <\/p>\n
      Another way to solve the problem, however, is to treat the sales for the current filter context as a table and filter it accordingly. Consider the example of sales for New York. Here\u2019s the underlying data for this city:<\/p>\n
      <\/p>\n
      The total figure for sales for New York for 2018 is 25.98 (18.98 + 7.00). One way to get to this would be to follow these steps when compiling the data for New York. Firstly, get the data for the filter context:<\/p>\n
      <\/p>\n
      Secondly, filter this data to include only those sales for 2018, by iterating down each row deciding whether to include it. This will include only the shaded area below:<\/p>\n
      <\/p>\n
      This leaves this table, which is the one whose sales Power BI will sum:<\/p>\n
      <\/p>\n
      Here\u2019s the formula to accomplish this:<\/p>\n
      2018 sales = SUMX(\n    FILTER(\n        Sales,\n        YEAR(Sales[SalesDate])=2018\n    ),\n    [Price]*[Quantity]\n)<\/pre>\n
      This will give exactly the same results as the formula using CALCULATE<\/code> above. The CALCULATE<\/code> function will run more quickly because it doesn\u2019t have to iterate down each row in the table testing a condition. At this point, you may be asking yourself what the point of the FILTER<\/code> function is. I\u2019ll return to this later in this article.<\/p>\n
      Linking tables within the Filter Function<\/h2>\n
      Take a look at how to show total sales for the USA for each city. The Sales<\/em>,<\/strong> City<\/em> and Country<\/em> tables are related as follows:<\/p>\n
      <\/p>\n
      What\u2019s needed is to iterate down the rows in the sales table, calculating the sales (price times quantity) for each but only where the country name is USA<\/em>. Here\u2019s the formula to do this:<\/p>\n
      American sales = SUMX(\n    FILTER(\n        Sales,\n        RELATED(Country[CountryName])=\"USA\"\n    ),\n    [Price]*[Quantity]\n)<\/pre>\n
      The expression gives these results:<\/p>\n
      <\/p>\n
      The question is \u2013 why use the RELATED<\/code> function when the DAX formulae using filter context automatically link tables together? The answer is that within this formula, row context, not filter context, is used. The shaded lines in the formula below iterate over each row in the Sales<\/em> table returned for the filter context, creating a row context for each:<\/p>\n
      <\/p>\n
      Because within the shaded bit of the formula DAX has to create a row context for each row in the sales table, it then has to use the RELATED<\/code> function to bring in the country name from the Country<\/em> table.<\/p>\n
      Combining Filters Without Nesting<\/h2>\n
      It\u2019s now time to look at how to combine criteria: how to show sales which happened in 2018 and which took place in the USA. I\u2019ll show in a bit how to do this by nesting one FILTER<\/code> function within another, but for now, I\u2019ll show ways to combine criteria. There are two basic ways to do this in DAX \u2013 either by using &&<\/code> or the AND<\/code> function (or if either of two conditions can be true, using ||<\/code> or the OR<\/code> function).<\/p>\n
      Here\u2019s a version of the measure using the AND<\/code> function:<\/p>\n
      2018 American sales = SUMX(\n    FILTER(\n        Sales,\n        AND(\n            RELATED(Country[CountryName])=\"USA\",          \n            YEAR(Sales[SalesDate])=2018\n        )\n    ),\n    [Price]*[Quantity]\n)<\/pre>\n
      Here\u2019s the same measure, but using the &&<\/code> symbols:<\/p>\n
      2018 American sales using && = SUMX(\n    FILTER(\n        Sales,\n        RELATED(Country[CountryName])=\"USA\" &&\n        YEAR(Sales[SalesDate])=2018\n    ),\n    [Price]*[Quantity]\n)<\/pre>\n
      Personally, I\u2019d use the AND<\/code> (or OR<\/code>) functions any time, as they work in the same way as their Excel counterparts, and it\u2019s easier to indent and comment formulae. However, you should use whichever floats your particular boat.<\/p>\n
      Even more sales have dropped from the figures:<\/p>\n
      <\/p>\n
      Combining Conditions by Nesting Functions<\/h2>\n
      The other way to solve this would have been to nest one function within another:<\/p>\n
      2018 American sales using nesting = SUMX(\n    FILTER(\n        FILTER(\n            Sales,\n            RELATED(Country[CountryName])=\"USA\"\n        ),\n        YEAR(Sales[SalesDate])=2018\n    ),\n    [Price]*[Quantity]\n)<\/pre>\n
      Consider what this does for the New York row in the table:<\/p>\n
      Filter context restricts the data to sales for the current city in question.<\/p>\n
      The inner FILTER<\/code> function iterates over each row in the table of data for the filter context, picking out only rows where the country is in the USA.<\/p>\n
      The outer FILTER<\/code> function then iterates over each row in the table of sales for the USA for the filter context and applies a further constraint that the sales year must be 2018.<\/p>\n
      Depending on your data, nesting FILTER<\/code> functions could speed up processing. If the vast majority of sales were outside the USA, the inner condition could eliminate nearly all rows for each city in the filter context, with the result that Power BI would only need to test the sales date for the few remaining rows.<\/p>\n
      Using ALL to Remove Any Filters<\/h2>\n
      Every example shown so far has taken the set of rows for the current filter context and applied additional constraints to pick out only certain rows. However, you can use the ALL<\/code> function when filtering to work with the entire table, rather than just the data for the current filter context. You could use this, for example, to show the percentage contribution of each city\u2019s sales to the grand total:<\/p>\n
      <\/p>\n
      Here\u2019s the formula for the above measure:<\/p>\n
      % of all sales = DIVIDE(\n    \/\/ calculate the total sales for the current filter context\n    SUMX(\n        Sales,\n        [Price]*[Quantity]\n    ),\n    \/\/ divide this by total sales for all cities\n    SUMX(\n        ALL(Sales),\n        [Price]*[Quantity]\n    )\n)<\/pre>\n
      Incidentally, if you\u2019re wondering how to get the nice percentage format, just select the measure you\u2019ve created:<\/p>\n
      <\/p>\n
      You can then set the formatting in the Modeling<\/strong> tab on the menu:<\/p>\n
      <\/p>\n
      The Difference Between CALCULATE and FILTER<\/h2>\n
      To see the difference between the way in which CALCULATE<\/code> and FILTER<\/code> filter data, consider this example:<\/p>\n
      <\/p>\n
      The first measure applies the filter context (so it only calculates sales for the city in question), and applies an additional constraint that the city should be New York:<\/p>\n
      New York FILTERED = CALCULATE(\n\u00a0\u00a0\u00a0\u00a0\/\/ work out total sales\n\u00a0\u00a0\u00a0\u00a0\/\/ for the filter context\n\u00a0\u00a0\u00a0\u00a0SUMX(\n        Sales,\n        [Price]*[Quantity]\n    ),\n\u00a0\u00a0\u00a0\u00a0\n\u00a0\u00a0\u00a0\u00a0\/\/ but whittle the filter context\n\u00a0\u00a0\u00a0\u00a0\/\/ down to show only those cities\n\u00a0\u00a0\u00a0\u00a0\/\/ within it called New York\n\u00a0\u00a0\u00a0\u00a0FILTER(\n        City,\n        City[CityName]=\"New York\"\n    )\n)<\/pre>\n
      The second measure replaces the filter context with a new constraint that the city should be New York, which results in the same figure appearing in every row:<\/p>\n
      New York CALCULATED = \n\u00a0\u00a0\u00a0\u00a0CALCULATE(\n\u00a0\u00a0\u00a0\u00a0\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\/\/ work out total sales\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\/\/ for the filter context\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0SUMX(\n            Sales,\n            [Price]*[Quantity]\n        ),\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\/\/ changing the city criteria\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\/\/ so it is New York, not \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\/\/ whatever the filter context\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\/\/ originally said\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0City[CityName]=\"New York\"\n)<\/pre>\n
      Debugging Using the FILTER Function (Method 1)<\/h2>\n
      To make debugging easier, first add a couple of calculated columns to the Sales<\/em> table, to give the city name and sales year. The formulae are shown below:<\/p>\n
      <\/p>\n
      Here\u2019s the formula for the City<\/em> column. It just looks up the name of each city in which sales took place:<\/p>\n
      <\/p>\n
      Here\u2019s the formula for the Sales<\/em> year<\/em> column. It gives the year in which each sale took place:<\/p>\n
      <\/p>\n
      These two columns will make it easier to check what\u2019s going on when debugging.<\/p>\n
      The FILTER<\/code> function creates virtual tables which, under normal circumstances, you never see, but you can use a tool like DAX Studio to show the rows these virtual tables contain. I\u2019ve covered how to download and use DAX Studio in a previous article<\/a> in this series, but here\u2019s a quick refresher. When you run DAX Studio, choose to connect to an open Power BI report:<\/p>\n
      <\/p>\n
      Type in a DAX query in the top right-hand window and press the F5 key to run this. The results will appear beneath it. For the example below, I\u2019m just listing out the contents of the Sales<\/strong> table:<\/p>\n
      <\/p>\n
      Incidentally, if you\u2019re wondering what those long date table names are, you\u2019re not the only one. I presume they are created behind the scenes to provide the built-in date hierarchy included in the March 2019 update of Power BI.<\/p>\n
      You can evaluate any table, including one which is returned from a filter function. A good thing to ask might be: which sales were in the United States? You can do this by copying this part of the measure you created earlier:<\/p>\n
      <\/p>\n
      Precede this with the word EVALUATE<\/code> in DAX Studio, and you\u2019ll get this:<\/p>\n
      <\/p>\n
      Run this to get the following output:<\/p>\n
      <\/p>\n
      That\u2019s looking good, so now you can repeat this technique with the outer bit of the FILTER<\/code> function:<\/p>\n
      <\/p>\n
      This gives only 3 rows:<\/p>\n
      <\/p>\n
      From this, it\u2019s easy to see why you get the figures for this measure.<\/p>\n
      Debugging Using the FILTER Function (Method 2)<\/h2>\n
      Another way to debug a DAX formula using the FILTER<\/code> function (or any other DAX formula, for that matter) is to use variables. I\u2019ve already covered this for scalar variables (ones holding a single value) in the previous article in this series on measures<\/a>, but did you know a variable can hold an entire table?<\/p>\n
      Here\u2019s another way to write the nested FILTER<\/code> function:<\/p>\n
      US sales in 2018 = \n\/\/ create a variable to hold the sales in the USA\nVAR UsaSalesTable =  FILTER(\n    Sales,\n    RELATED(Country[CountryName])=\"USA\"\n)\n\/\/ create another variable to filter this to show\n\/\/ only sales in 2008\nVAR UsaSales2018 = FILTER(\n    UsaSalesTable,\n    YEAR(Sales[SalesDate])=2018\n)\n\/\/ finally, calculates sales for these figures\nRETURN SUMX(\n    UsaSales2018,\n    [Price]*[Quantity]\n)<\/pre>\n
      The advantage of breaking the complicated formula down into different parts is that you could then test each in isolation.<\/p>\n
      Why Would You Use the FILTER Function?<\/h2>\n
      I promised I would return to this question: why would you use the FILTER<\/code> function when the CALCULATE<\/code> function seems to offer a better alternative? There are at least four advantages:<\/p>\n
      I\u2019ve already shown that it\u2019s easier to debug DAX expressions that use the FILTER<\/code> function.<\/p>\n
      I think expressions using the FILTER<\/code> function are easier to understand than equivalent expressions just using CALCULATE<\/code>.<\/p>\n
      Learning the FILTER<\/code> function will help you to understand the EARLIER<\/code> function, which will be the subject of the next article in this series.<\/p>\n
      There are some problems which the CALCULATE<\/code> function won\u2019t solve (an example follows).<\/p>\n
      To illustrate the last point, suppose that you want to create a measure showing total sales for cities having two or more purchases. Here are the figures that this should return:<\/p>\n
      <\/p>\n
      There are no sales recorded for Chicago, LA and Rio in the new measure because they each only witnessed a single sale.<\/p>\n
      Assume in all of the following that [Number of purchases]<\/code> is a measure with this formula:<\/p>\n
      Number of purchases = COUNTROWS(Sales)<\/pre>\n
      Here\u2019s a measure which you could use to try to solve this problem (although it won\u2019t work):<\/p>\n
      Sales for multiple purchases = CALCULATE(\n    \/\/ calculate total sales where ...\n    SUMX(\n        Sales,\n        [Price]*[Quantity]\n    ),\n    \/\/ ... the number of purchases is more than 1\n    [Number of purchases] > 1\n)<\/pre>\n
      If you type in this measure, you\u2019ll see the following error message:<\/p>\n
      <\/p>\n
      This isn\u2019t a brilliant description of the problem, which is that you can\u2019t use a measure in the filtering part of a CALCULATE<\/code> function; you can only refer to columns. You can, however, solve this problem by rewriting it to incorporate a FILTER<\/code> function:<\/p>\n
      Sales for multiple purchases = CALCULATE(\n    \/\/ calculate total sales but ...\n    SUMX(\n        Sales,\n        [Price]*[Quantity]\n    ),\n    \/\/ only where the number of \n    \/\/ purchases is more than 1\n    FILTER(\n        City,\n        [Number of purchases] > 1\n    )\n)<\/pre>\n
      This will calculate total sales, but only for those cities where the number of purchases was more than 1.<\/p>\n
      Summary<\/h2>\n
      The FILTER function in DAX allows you to iterate down the rows of any table, creating a row context for each and testing whether the row should be included in your calculation. You can combine filters using keywords like AND and OR and also nest one filter within another. The FILTER function allows you to perform some tasks which the CALCULATE function can\u2019t reach, and also (in my opinion) lets you create formulae which are easier to understand.<\/p>\n","protected":false},"excerpt":{"rendered":"
      In the previous article of this series, Andy Brown of Wise Owl Training explained how to use the oh-so-important CALCULATE function in DAX to make changes to the default filter context within a formula. This article shows how you can use the FILTER function to do something similar and explains the differences between the two approaches. …<\/p>\n","protected":false},"author":9783,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143528],"tags":[5134],"coauthors":[6782],"class_list":["post-83924","post","type-post","status-publish","format-standard","hentry","category-bi-sql-server","tag-sql-prompt"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/83924","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/9783"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=83924"}],"version-history":[{"count":5,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/83924\/revisions"}],"predecessor-version":[{"id":107297,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/83924\/revisions\/107297"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=83924"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=83924"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=83924"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=83924"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}