banner



Home  ›  How To Ignore One Filter And Use Another Instead In Ssas Tabular Dax Expressions

How To Ignore One Filter And Use Another Instead In Ssas Tabular Dax Expressions

Written By Friday, April 15, 2022 Add Comment Edit

In the previous article of this series, Andy Brown of Wise Owl Preparation 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 office to do something like and explains the differences betwixt the two approaches.

The series then far:

  1. Creating Calculated Columns Using DAX
  2. Creating Measures Using DAX
  3. Using the DAX Summate and Values Functions
  4. Using the FILTER Function in DAX
  5. Corking DAX – the EARLIER and RANKX Functions
  6. Using Calendars and Dates in Ability BI
  7. Creating Time-Intelligence Functions in DAX

The FILTER function works quite differently than the CALCULATE function explained in the previous article. It returns a table of the filtered rows, and sometimes it is the better arroyo to have.

I'll spend most of this article explaining how to create the following measures:

The columns in a higher place show, respectively:

  1. The city name;
  2. Total sales for the city in question (the filter context);
  3. Total sales for the city in question for 2018;
  4. Total sales for the urban center in question for the Us;
  5. Full sales for the city in question for the USA for 2018; and
  6. The percentage each city'due south sales contributes to the total.

Offset, I'll show you how to set up up the example. And so I'll dive into the syntax of the FILTER office. I'll stop by highlighting the differences between the FILTER and CALCULATE functions.

A Quick Refresher

To work through the examples in this commodity, you'll need to create a simple Power BI report containing a single table and then create and bear witness a series of measures. Hither's a quick run-through of how to get started.

Kickoff, 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 or the Excel workbook. You should at present have something like this (if your diagram looks a bit dissimilar, you may not take updated your case of Ability BI to include the March 2019 update, which included the new Model View):

Now create a table in Report view to list out the city names. Make sure the Table visualization is selected and click CityName in the Fields listing.

Switch to the Abode ribbon and select Enter Information. This volition add a new table to your report to comprise your measures. To understand why you might want to do this, run into this previous article in this series:

Give this tabular array a proper noun. Here I've chosen mine All measures:

Click Load. Now add together the following measure to your All measures table. You can right-click on the table and cull New measure out to do this:

Choose to display this measure in your tabular array:

You should now be able to see the total value of sales for each city:

What happens if yous want to show the sales for American cities but? Or sales taking place in 2018 but?

The FILTER Function

The measure for the sales column shown above, giving total sales for each city, is equally follows:

What this does (as readers of this series of articles will know) is to iterate down the rows in the Sales table, calculating the cost multiplied by the quantity for each and summing the result for each city to get this:

To get the sales in 2008, you could use a CALCULATE office and so that this measure would work:

This takes the filter context for each city, and further reduces it to consider only those rows where the sales occurred in 2018 to go this:

Some other way to solve the problem, withal, is to treat the sales for the current filter context every bit a tabular array and filter information technology accordingly. Consider the example of sales for New York. Here's the underlying data for this urban center:

The full effigy for sales for New York for 2018 is 25.98 (18.98 + 7.00). One way to get to this would exist to follow these steps when compiling the data for New York. Firstly, get the data for the filter context:

Secondly, filter this data to include merely those sales for 2018, by iterating downwards each row deciding whether to include information technology. This will include only the shaded surface area beneath:

This leaves this tabular array, which is the one whose sales Power BI volition sum:

Hither's the formula to accomplish this:

This volition requite exactly the same results as the formula using Calculate in a higher place. The CALCULATE office will run more speedily considering information technology doesn't have to iterate down each row in the table testing a condition. At this point, you may be request yourself what the point of the FILTER part is. I'll return to this later in this commodity.

Linking tables within the Filter Function

Have a look at how to show total sales for the USA for each urban center. The Sales , Urban center and Country tables are related as follows:

What's needed is to iterate downwardly the rows in the sales table, calculating the sales (price times quantity) for each just merely where the country proper name is USA. Here's the formula to practice this:

The expression gives these results:

The question is – why use the RELATED office when the DAX formulae using filter context automatically link tables together? The reply is that within this formula, row context, non filter context, is used. The shaded lines in the formula below iterate over each row in the Sales tabular array returned for the filter context, creating a row context for each:

Because within the shaded fleck of the formula DAX has to create a row context for each row in the sales table, it then has to utilize the RELATED office to bring in the country name from the Country table.

Combining Filters Without Nesting

It'due south now time to look at how to combine criteria: how to show sales which happened in 2018 and which took place in the U.s.. I'll testify in a bit how to do this by nesting one FILTER function within some other, just for at present, I'll evidence means to combine criteria. There are two basic ways to do this in DAX – either by using && or the AND function (or if either of 2 weather can exist true, using || or the OR role).

Here's a version of the measure using the AND function:

Here's the aforementioned measure out, simply using the && symbols:

Personally, I'd use the AND (or OR) functions any time, as they work in the same way every bit their Excel counterparts, and it's easier to indent and annotate formulae. Nevertheless, yous should use whichever floats your particular gunkhole.

Fifty-fifty more sales take dropped from the figures:

Combining Weather by Nesting Functions

The other way to solve this would take been to nest one office within another:

Consider what this does for the New York row in the table:

Filter context restricts the data to sales for the current metropolis in question.

The inner FILTER function iterates over each row in the tabular array of data for the filter context, picking out only rows where the country is in the USA.

The outer FILTER function and so 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.

Depending on your information, nesting FILTER functions could speed up processing. If the vast bulk of sales were outside the Us, the inner condition could eliminate nearly all rows for each city in the filter context, with the event that Power BI would only need to test the sales date for the few remaining rows.

Using ALL to Remove Whatever Filters

Every case shown so far has taken the gear up of rows for the current filter context and applied boosted constraints to option out just certain rows. Notwithstanding, you can use the ALL role when filtering to work with the entire table, rather than only the data for the current filter context. You could use this, for case, to show the pct contribution of each city's sales to the thou total:

Hither's the formula for the to a higher place measure:

Incidentally, if you're wondering how to get the squeamish pct format, just select the mensurate you've created:

You tin can then set the formatting in the Modeling tab on the menu:

The Difference Between CALCULATE and FILTER

To come across the difference between the way in which Calculate and FILTER filter data, consider this example:

The first measure applies the filter context (so it simply calculates sales for the city in question), and applies an additional constraint that the urban center should be New York:

The 2d 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:

Debugging Using the FILTER Function (Method 1)

To make debugging easier, first add a couple of calculated columns to the Sales table, to give the city proper name and sales yr. The formulae are shown beneath:

Here's the formula for the City cavalcade. It merely looks up the name of each urban center in which sales took place:

Here'south the formula for the Sales yr column. It gives the twelvemonth in which each auction took place:

These two columns will make information technology easier to check what's going on when debugging.

The FILTER office creates virtual tables which, under normal circumstances, you never see, just you can use a tool like DAX Studio to show the rows these virtual tables comprise. I've covered how to download and use DAX Studio in a previous article in this serial, but here's a quick refresher. When you run DAX Studio, choose to connect to an open Power BI report:

Type in a DAX query in the top correct-paw window and press the F5 key to run this. The results will appear beneath it. For the example below, I'thou simply listing out the contents of the Sales tabular array:

Incidentally, if you're wondering what those long date table names are, you're not the but one. I assume they are created behind the scenes to provide the built-in date hierarchy included in the March 2019 update of Power BI.

You can evaluate any tabular array, including one which is returned from a filter function. A good thing to ask might exist: which sales were in the United states? You lot can do this by copying this part of the mensurate you lot created before:

Precede this with the word EVALUATE in DAX Studio, and you'll get this:

Run this to go the following output:

That'southward looking expert, and then now you can repeat this technique with the outer bit of the FILTER function:

This gives just iii rows:

From this, it'southward easy to encounter why y'all become the figures for this mensurate.

Debugging Using the FILTER Function (Method 2)

Another style to debug a DAX formula using the FILTER function (or any other DAX formula, for that thing) is to use variables. I've already covered this for scalar variables (ones holding a unmarried value) in the previous article in this serial on measures, only did you know a variable can concord an unabridged table?

Here's another way to write the nested FILTER office:

The reward of breaking the complicated formula down into different parts is that you could and so examination each in isolation.

Why Would Y'all Apply the FILTER Function?

I promised I would return to this question: why would y'all utilize the FILTER function when the Calculate function seems to offer a better alternative? There are at least iv advantages:

I've already shown that it's easier to debug DAX expressions that apply the FILTER role.

I think expressions using the FILTER function are easier to understand than equivalent expressions but using CALCULATE.

Learning the FILTER part will help you to understand the Before function, which volition exist the discipline of the next article in this series.

In that location are some problems which the Summate function won't solve (an instance follows).

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:

There are no sales recorded for Chicago, LA and Rio in the new measure out because they each only witnessed a single auction.

Assume in all of the following that [Number of purchases] is a measure with this formula:

Here's a measure which y'all could use to try to solve this problem (although it won't work):

If you type in this measure, y'all'll see the following error bulletin:

This isn't a vivid clarification of the trouble, which is that y'all can't employ a mensurate in the filtering function of a CALCULATE function; you can only refer to columns. You can, however, solve this trouble by rewriting it to comprise a FILTER function:

This will calculate total sales, but only for those cities where the number of purchases was more than than one.

Summary

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. Yous can combine filters using keywords like AND and OR and also nest one filter within some other. The FILTER function allows y'all to perform some tasks which the CALCULATE part can't reach, and also (in my opinion) lets you lot create formulae which are easier to understand.

How To Ignore One Filter And Use Another Instead In Ssas Tabular Dax Expressions,

Source: https://www.red-gate.com/simple-talk/databases/sql-server/bi-sql-server/using-the-filter-function-in-dax/

Posted by: binfordalthatede.blogspot.com

0 Response to "How To Ignore One Filter And Use Another Instead In Ssas Tabular Dax Expressions"

Post a Comment

Subscribe to: Post Comments (Atom)

Iklan Atas Artikel

Iklan Tengah Artikel 1

Iklan Tengah Artikel 2

Iklan Bawah Artikel