## Tag Archives: Filter

## FILTER() – When, Why, & How to Use It (in Power BI too)!

**Below, you will find one of our all-time favorite Power BI techniques. ** In the ten (yes ten) years we’ve been operating this site, we’ve written over 1,000 articles on Power BI. Hard to imagine – even for us – but true.

**Years ago, we first wrote up this technique in the context of Power Pivot** – Power BI’s cousin and predecessor, which we like to call “Power BI in Excel.”

**Since the two products share the same brains (DAX and M), this technique is more relevant today than ever.** Everything below is 100% legit for Power BI – the same data model “shape,” the same DAX, etc. – but now you can use it to power up your Power BI visuals, and not just PivotTables. Enjoy!

A recent comment/question alerted me to the fact that I’ve never devoted a post just to this very useful (and often misunderstood) function. Time to correct that.

### The #1 Reason to Use FILTER – When CALCULATE Breaks Down

**Does this Mysterious Error Look Familiar?**

**Different Formula, Same Error**

### What’s Wrong With Those Formulas?

The thing both of those formulas have in common is that they are using a measure in the filter argument of the CALCULATE function. In both examples here, I’ve highlighted the offending measure in yellow.

CALCULATE([Sightings per Year], [Avg Sighting Length in Mins]>6)

CALCULATE([Sightings per Year],

Observations[TTL Min]>[Avg Sighting Length in Mins])

In the first formula, I was trying to use a measure on the left side of the comparison, and in the second, I was trying to use a measure on the right side of the comparison. Both are illegal.

**CALCULATE expects its filter arguments to take the form of Column=Fixed Value, or >Fixed Value, <= Fixed Value, etc., where “Fixed Value” is a specific number (like 6), a specific text string (like “Regular”), or a specific date.** So my first formula violates the rule that a column name is required on the left. And my second formula violates the rule where a fixed value (not an expression or a measure) is required on the right.

**CALCULATE refuses to let you use variable expressions like measures in these filter arguments largely because “vanilla” CALCULATE is intended to always be fast**, and once you start including expressions in these comparisons, your formulas might run a LOT slower. So this is a good rule really – it forces you to stop and think before accidentally doing something bad. The error message, of course, could and should be a lot better.

For a bit more explanation on this, **see this brief post**.

### What’s the Solution?

If you look at those two illegal formulas above, they both reflect a perfectly valid intent. The first formula is attempting to ask for “how many sightings per year would I report if we just counted sightings that lasted more than 6 minutes” and the second is asking for “how many sightings per year are above average in length.”

I’m almost regretting my selection of those examples because they are a bit more complex than necessary to make the fundamental points. But hey, too late now to change them, so I’ll move quickly.

In the first example, the Avg Sighting Length measure is actually based on a column in my Observations table – each UFO sighting has a [TTL Min] column. So I could rewrite that filter in the calculate as Observations[TTL Min] > 6 and everything is fixed.

But let’s say I wanted to filter out entire States where the average sighting length was > 6. Since I don’t have a column in my States table that does that, it’s sensible to use the measure, and that forces me to use FILTER because **FILTER does allow me to use measures in my comparisons:**

CALCULATE([Sightings per Year],

FILTER(States, [Avg Sighting Length in Mins]>6)

)

See that? The highlighted section took one of the filter arguments to CALCULATE and replaced it with a call to the FILTER function. The syntax of FILTER is pretty simple but is explained below.

In my second example, where a measure was used on the right side of the comparison, the formula gets rewritten as:

CALCULATE([Sightings per Year],

FILTER(Observations,

Observations[TTL Min]>[Avg Sighting Length in Mins])

)

So there you go. When you want to use a measure, or an expression like AVERAGE(Observations[TTL Mins]), you have to call in the FILTER function. More details follow, starting with the simplest information and moving to the most subtle of characteristics.

### How does FILTER() Work?

The syntax for the FILTER function is FILTER(*TableToFilter*, *FilterExpression*). Pretty simple.

**For simple purposes, if you understand the gist of the above, and then points 1 and 2 below, you are good to go.** If you want to understand more of the details over time, I recommend revisiting points 3-5.

**FILTER() takes a***TableToFilter*and a*FilterExpression*and returns all rows from that*TableToFilter*that match the*FilterExpression*.- In the example above,
*TableToFilter*is ALL(Periods) - and
*FilterExpression*is Periods[Year]=MAX(Periods[Year])-1

- In the example above,
**FILTER() steps through the***TableToFilter*one row at a time.- And for each row, it evaluates the
*FilterExpression*. If the expression evaluates to true, the row is “kept.” If not, it is filtered out. - Because FILTER() goes one row at a time, it can be quite slow if you use it against a large table. When I say “large” that is, of course, subjective. A few thousand rows are fine in my experience. A million is not. Do not use FILTER() against your fact table.

- And for each row, it evaluates the
**The***FilterExpression*typically takes the form of Table[Column] = <expression>- The comparison operator doesn’t have to be “=.” It can also be <, >, <=, >=, <>
- The expression on the right-hand side of
*FilterExpression*can be “rich.” This is VERY useful. In a simple CALCULATE, the right side of each filter expression has to be simple, like a literal number (9) or a string (“Standard”). The fact that FILTER() allows for rich expressions here is one of the most common reasons I use FILTER(). - The Table[Column] in the filter expression is a column in the
*TableToFilter*. If you are filtering the Periods table, it makes sense that you are testing some property of each row in Periods. I can’t think of a sensible reason to use a column here that is NOT from*TableToFilter.*(Insert “boot signal” here, maybe the Italians can address this).

**FILTER() ignores everything else going on in your formula and acts completely on its own.**- For example, our overall formula sets ALL(Periods) as the first argument to CALCULATE.
- The FILTER()’s that come after that do NOT pay any attention to other arguments, however, including that ALL(Periods).
- In other words, the FILTER() functions are still operating against the original filter context from the pivot! If the pivot is sliced to Year=2009, then the FILTER() function starts with the Periods table already pre-filtered to just 2009.
- This is why each of my FILTER()’s uses ALL(Periods) for
*TableToFilter.*I have to repeat the “expand” step so that my FILTER() is also working from a clean slate.

**Even though each FILTER() operates on its own, their results then “stack up” in the overall formula.**- Even though FILTER() RETURNS a set of rows that matched the
*FilterExpression,*it actually REMOVES rows from the overall filter context. - This sounds tricky, but really, it isn’t.
- Let’s say our
*TableToFilter*contains 6 rows: A, B, C, D, E, and F. - And our overall formula contains two FILTER() clauses that both operate on the same
*TableToFilter*, just like our overall formula near the beginning of this post. - Let’s also say that the first FILTER() returns rows A, B, C, and D.
- And the second FILTER() returns rows C, D, E, and F.
- The net result is that only rows C and D are left “alive” in the overall filter context of the formula.
- So one way to think of this is that FILTER()s “stack up” on top of each other.
- Another way to think of it is that even though the first filter RETURNED rows A, B, C, and D, its real effect was to
*REMOVE all other rows*(E and F) from consideration.

- Even though FILTER() RETURNS a set of rows that matched the

## Using the FILTER Function in DAX

**The series so far:**

- Creating Calculated Columns Using DAX
- Creating Measures Using DAX
- Using the DAX Calculate and Values Functions
- Using the FILTER Function 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 approach to take.

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

The columns above show, respectively:

- The city name;
- Total sales for the city in question (the filter context);
- Total sales for the city in question for 2018;
- Total sales for the city in question for the USA;
- Total sales for the city in question for the USA for 2018; and
- The percentage each city’s sales contributes to the total.

First, I’ll show you how to set up the example. Then I’ll dive into the syntax of the `FILTER`

function. I’ll finish by highlighting the differences between the `FILTER`

and `CALCULATE`

functions.

## A Quick Refresher

To work through the examples in this article, you’ll need to create a simple Power BI report containing a single table and then create and show a series of measures. Here’s a quick run-through of how to get started.

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 or the Excel workbook. 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):

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* list.

Switch to the *Home* ribbon and select *Enter* *Data*. 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 in this series:

Give this table a name. Here I’ve called mine *All measures*:

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

Sales = SUMX( // multiply the price of each transaction by // its quantity, and sum the result Sales, [Price]*[Quantity] ) |

Choose to display this measure in your table:

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

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

## The FILTER Function

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

Sales = SUMX( Sales, [Price]*[Quantity] ) |

What this does (as readers of this series of articles will know) is to iterate down the rows in the *Sales* table, calculating the price 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`

function so that this measure would work:

2018 sales using CALCULATE = CALCULATE( SUMX( Sales, [Price]*[Quantity] ), YEAR(Sales[SalesDate]) = 2018 ) |

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:

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’s the underlying data for this city:

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:

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:

This leaves this table, which is the one whose sales Power BI will sum:

Here’s the formula to accomplish this:

2018 sales = SUMX( FILTER( Sales, YEAR(Sales[SalesDate])=2018 ), [Price]*[Quantity] ) |

This will give exactly the same results as the formula using `CALCULATE`

above. The `CALCULATE`

function will run more quickly because it doesn’t 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`

function is. I’ll return to this later in this article.

## Linking tables within the Filter Function

Take a look at how to show total sales for the USA for each city. The *Sales***,** *City* and *Country* tables are related as follows:

What’s 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*. Here’s the formula to do this:

American sales = SUMX( FILTER( Sales, RELATED(Country[CountryName])=“USA” ), [Price]*[Quantity] ) |

The expression gives these results:

The question is – why use the `RELATED`

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* table returned for the filter context, creating a row context for each:

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`

function to bring in the country name from the *Country* table.

## Combining Filters Without Nesting

It’s 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’ll show in a bit how to do this by nesting one `FILTER`

function within another, but for now, I’ll show ways to combine criteria. There are two basic ways to do this in DAX – either by using `&&`

or the `AND`

function (or if either of two conditions can be true, using `||`

or the `OR`

function).

Here’s a version of the measure using the `AND`

function:

1 2 3 4 5 6 7 8 9 10 |
2018 American sales = SUMX( FILTER( Sales, AND( RELATED(Country[CountryName])=“USA”, YEAR(Sales[SalesDate])=2018 ) ), [Price]*[Quantity] ) |

Here’s the same measure, but using the `&&`

symbols:

2018 American sales using && = SUMX( FILTER( Sales, RELATED(Country[CountryName])=“USA” && YEAR(Sales[SalesDate])=2018 ), [Price]*[Quantity] ) |

Personally, I’d use the `AND`

(or `OR`

) functions any time, as they work in the same way as their Excel counterparts, and it’s easier to indent and comment formulae. However, you should use whichever floats your particular boat.

Even more sales have dropped from the figures:

## Combining Conditions by Nesting Functions

The other way to solve this would have been to nest one function within another:

1 2 3 4 5 6 7 8 9 10 |
2018 American sales using nesting = SUMX( FILTER( FILTER( Sales, RELATED(Country[CountryName])=“USA” ), YEAR(Sales[SalesDate])=2018 ), [Price]*[Quantity] ) |

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

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

The inner `FILTER`

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.

The outer `FILTER`

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.

Depending on your data, nesting `FILTER`

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.

## Using ALL to Remove Any Filters

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`

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’s sales to the grand total:

Here’s the formula for the above measure:

1 2 3 4 5 6 7 8 9 10 11 12 13 |
% of all sales = DIVIDE( // calculate the total sales for the current filter context SUMX( Sales, [Price]*[Quantity] ), // divide this by total sales for all cities SUMX( ALL(Sales), [Price]*[Quantity] ) ) |

Incidentally, if you’re wondering how to get the nice percentage format, just select the measure you’ve created:

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

## The Difference Between CALCULATE and FILTER

To see the difference between the way in which `CALCULATE`

and `FILTER`

filter data, consider this example:

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:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
New York FILTERED = CALCULATE( // work out total sales // for the filter context SUMX( Sales, [Price]*[Quantity] ),
// but whittle the filter context // down to show only those cities // within it called New York FILTER( City, City[CityName]=“New York” ) ) |

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:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
New York CALCULATED = CALCULATE(
// work out total sales // for the filter context SUMX( Sales, [Price]*[Quantity] ),
// changing the city criteria // so it is New York, not // whatever the filter context // originally said City[CityName]=“New York” ) |

## 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 name and sales year. The formulae are shown below:

Here’s the formula for the *City* column. It just looks up the name of each city in which sales took place:

Here’s the formula for the *Sales* *year* column. It gives the year in which each sale took place:

These two columns will make it easier to check what’s going on when debugging.

The `FILTER`

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’ve covered how to download and use DAX Studio in a previous article in this series, 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 right-hand window and press the F5 key to run this. The results will appear beneath it. For the example below, I’m just listing out the contents of the **Sales** table:

Incidentally, if you’re wondering what those long date table names are, you’re 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.

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:

Precede this with the word `EVALUATE`

in DAX Studio, and you’ll get this:

Run this to get the following output:

That’s looking good, so now you can repeat this technique with the outer bit of the `FILTER`

function:

This gives only 3 rows:

From this, it’s easy to see why you get the figures for this measure.

## Debugging Using the FILTER Function (Method 2)

Another way to debug a DAX formula using the `FILTER`

function (or any other DAX formula, for that matter) is to use variables. I’ve already covered this for scalar variables (ones holding a single value) in the previous article in this series on measures, but did you know a variable can hold an entire table?

Here’s another way to write the nested `FILTER`

function:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
US sales in 2018 = // create a variable to hold the sales in the USA VAR UsaSalesTable = FILTER( Sales, RELATED(Country[CountryName])=“USA” ) // create another variable to filter this to show // only sales in 2008 VAR UsaSales2018 = FILTER( UsaSalesTable, YEAR(Sales[SalesDate])=2018 ) // finally, calculates sales for these figures RETURN SUMX( UsaSales2018, [Price]*[Quantity] ) |

The advantage of breaking the complicated formula down into different parts is that you could then test each in isolation.

## Why Would You Use the FILTER Function?

I promised I would return to this question: why would you use the `FILTER`

function when the `CALCULATE`

function seems to offer a better alternative? There are at least four advantages:

I’ve already shown that it’s easier to debug DAX expressions that use the `FILTER`

function.

I think expressions using the `FILTER`

function are easier to understand than equivalent expressions just using `CALCULATE`

.

Learning the `FILTER`

function will help you to understand the `EARLIER`

function, which will be the subject of the next article in this series.

There are some problems which the `CALCULATE`

function won’t solve (an example 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 because they each only witnessed a single sale.

Assume in all of the following that `[Number of purchases]`

is a measure with this formula:

Number of purchases = COUNTROWS(Sales) |

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

Sales for multiple purchases = CALCULATE( // calculate total sales where … SUMX( Sales, [Price]*[Quantity] ), // … the number of purchases is more than 1 [Number of purchases] > 1 ) |

If you type in this measure, you’ll see the following error message:

This isn’t a brilliant description of the problem, which is that you can’t use a measure in the filtering part of a `CALCULATE`

function; you can only refer to columns. You can, however, solve this problem by rewriting it to incorporate a `FILTER`

function:

1 2 3 4 5 6 7 8 9 10 11 12 13 |
Sales for multiple purchases = CALCULATE( // calculate total sales but … SUMX( Sales, [Price]*[Quantity] ), // only where the number of // purchases is more than 1 FILTER( City, [Number of purchases] > 1 ) ) |

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

## 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. 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’t reach, and also (in my opinion) lets you create formulae which are easier to understand.

## #PowerQuery – Filter by a dynamically set of columns

A few days ago, I ran into a problem where I had to filter a product table by a dynamically set of columns – so in one solution it could be filtering by field x and y and in other solutions it had to be by field a, b and c.

In this case only rows that where not blank sin either of these columns should be included in the result.

Hmmm… how can we do this using Power Query –

Here is the demo data

And if we only want the rows where Field A and Field B has the value “xx” we could add a filter statement (this can only by done if you modify the SelectRows formula manually! )

*= Table.SelectRows(#”Changed Type”, each ([Field A] <> “” or [Field B] <> “”))
*

But what if we wanted to make the fields dynamically

Let’s start by creating a list containing the field names we want to filter

This list could also be retrieved by extracting data from a config file or a database if you would make it even more flexible – just remember it must be at list

Now you should know that any row in a table is a **Record**

And each record has fields and Power Query has functions related to Records.

https://docs.microsoft.com/en-us/powerquery-m/record-functions

One of this is the **Record.SelectFields** ( https://docs.microsoft.com/en-us/powerquery-m/record-selectfields ) and this function can be used to select specific fields in a record

So lets add a custom column with

*= Table.AddColumn(#”Filtered Rows”, “FilterMultipleColumns”, each Record.SelectFields(_, FieldList))
*

The first argument uses the _ statement and thereby referring to the current record – and the second is over list of fields

And as you can see when selecting a cell the record in the column now contains only of the fields we have specified in our list.

Now we turn this in to a list by using the function **Record.ToList **in our AddColumn statement

*= Table.AddColumn(#”Filtered Rows”, “FilterMultipleColumns”, each Record.ToList(Record.SelectFields(_, FieldList)))
*

Notice that we now have a list in the column instead of a record

Next step is to use the function **List.MatchesAny** to check whether the contains our filter

*= Table.AddColumn(#”Changed Type”, “FilterMultipleColumns”, each List.MatchesAny(Record.ToList(Record.SelectFields(_, FieldList)), each _ <> “”))*

The second argument is then our filter for all fields and it will return TRUE or FALSE if the criteria is met.

And finally, we can filter the rows using the added Column and only include rows that return true

And if we want to filter by other columns/fields, we change the list

And it works

Hope you find this useful and let me know if you do

Query On !

## Power BI Report URL filter improvements

Power BI now offers enhanced filtering capabilities that let you define more complex report-level filters as query string parameters directly in the report URL, as the following screenshot illustrates. This example shows the Retail Analysis sample with a report-level filter to display results only for the item category “050-Shoes”, for the year 2014 (the last year with data in this sample solution), and only for new stores in the sales territories of Kentucky and Ohio. You can see the applied report-level filter in the Filters pane.

The following query string defines this filter definition. It demonstrates that you can filter on multiple fields and different data types and illustrates how to escape column names that have special characters. The sequence _x0020_ escapes a space to specify the column name “Store Type” in OData-compliant syntax. The filter definition also shows how to use the in operator to narrow down the sales territory.

?filter=Time/Date ge 2014-01-01 and Time/Date lt 2015-01-01 and Store/Store_x0020_Type eq ‘New Store’ and Item/Category eq ‘050-Shoes’ and Store/Territory in (‘KY’, ‘OH’)

Power BI Report URL filters follow the OData syntax to be consistent with the filtering methods in other Power BI areas (such as, filter statements in Power BI cmdlets), but not all OData filter capabilities are supported in Report URL filters. For a good overview of supported operations, see the article Filter a report using query string parameters in the URL in the product documentation.

Report URL filters can come in handy if you want to create shortcuts to pre-filtered reports. In Windows, for instance, you could create Desktop shortcuts for quick and easy access to the information you care about most. You could also add the URLs to the Favorites list of your Web browser or use them as custom URLs with dashboard tiles to view the filtered report directly from a tile. Another option is to create a table in a Power BI solution with the desired report URLs, as explained in the article Hyperlinks in tables in the product documentation.

It is even possible to construct the filter URL dynamically in a Power BI solution by using a DAX measure, as in the screenshot below. This DAX expression checks if the user applied a filter on the Store/Territory column, such as by using a slicer, and generates the filter URL using the available values if this is the case.

Of course, the above is a simplistic example, but it illustrates the possibilities to generate filtered report URLs dynamically. For your reference, here’s the DAX expression to implement this measure.

filteredURL = IF(ISFILTERED(Store[Territory]), “https://app.powerbi.com/groups/me/reports/beb3e658-8372-4bfe-8cb2-914287c75b62/ReportSection3?filter=Store/Territory in (‘” & CONCATENATEX(VALUES(Store[Territory]), Store[Territory], “‘,'”) & “‘)”)

And that’s it for a quick introduction of the enhanced URL filtering capabilities that you can use in your Power BI solutions. For additional information, read the Filter a report using query string parameters in the URL article in the product documentation, and stay tuned for more filtering improvements coming soon.

## NetFT Virtual Adapter Performance Filter

In this blog I will discuss what the NetFT Virtual Adapter Performance Filter is and the scenarios when you should or should not enable it.

The Microsoft Failover Cluster Virtual Adapter (NetFT) is a virtual adapter used by the Failover Clustering feature to build fault tolerant communication routes between nodes in a cluster for intra-cluster communication.

When the Cluster Service communicates to another node in the cluster, it sends data down over TCP to the NetFT virtual adapter. NetFT then sends the data over UDP down to the physical network card, which then sends it over the network to another node. See the below diagram:

When the data is received by the other node, it follows the same flow. Up the physical adapter, then to NetFT, and finally up to the Cluster Service. The NetFT Virtual Adapter Performance Filter is a filter in Windows Server 2012 and Windows Server 2012 R2 which inspects traffic inbound on the physical NIC and then reroutes cluster traffic addressed for NetFT directly to the NetFT driver. This bypasses the physical NIC UDP / IP stack and delivers increased cluster network performance.

## When to Enable the NetFT Virtual Adapter Performance Filter

The NetFT Virtual Adapter Performance Filter is disabled by default. The filter is disabled because it can cause issues with Hyper-V clusters which have a Guest Cluster running in VMs running on top of them. Issues have been seen where the NetFT Virtual Adapter Performance Filter in the host incorrectly routes NetFT traffic bound for a guest VM to the host. This can result in communication issues with the guest cluster in the VM. More details can be found in this article:

https://support.microsoft.com/en-us/kb/2872325

If you are deploying any workload **other** than Hyper-V with guest clusters, enabling the NetFT Virtual Adapter Performance Filter will optimize and improve cluster performance.

## Windows Server 2016

Due to changes in the networking stack in Windows Server 2016, the NetFT Virtual Adapter Performance Filter has been removed.

Thanks!

Elden Christensen

Principal PM Manager

High-Availability & Storage

Microsoft

## Stop Exporting, Analyze Data Inside Dynamics CRM with Filter Panels

Do you export data from Microsoft Dynamics CRM into Excel so that you can slice, dice and analyze it? While this does work, it is time consuming and prone to errors, it can’t easily be shared and the data is quickly out of date.

There is a better way to analyze data, with the flexibility of Microsoft Excel, right inside Microsoft Dynamics CRM using Filter Panels in the AbleBridge Editable CRM Grid tool.

This add on tool allows for inline editing, grouping and calculations and turns Dynamics CRM forms into Excel-like grids. But many people do not realize how valuable it can be to go a step further using Filter Panels.

**What is a Filter Panel?**

Picture an Excel spreadsheet and above the cells you have 10 fields, which are basically parameters. If you point and click and change the fields the results in the spreadsheet below will automatically change and reflect the information with the new parameters. In this way you can slice and dice information any way you like.

Now picture that this data is actually a page in Dynamics CRM, in an editable grid format, with filter panels across the top.

**Using Filter Panels to Analyze Sales Data**

What if you wanted quickly to see a list of opportunities in Dynamics CRM with total sales filtered by territory.

Using EditAble CRM Grid with Filter Panels, the fields at the top of the Dynamics CRM screen could be set to show Worldwide Sales and Territories. Without any fields filled in, you would see all the worldwide opportunities within the grid. A big laundry list of them.

But, let’s say you want to look only at North America. Click the filter and the data will adjust to show all the opportunities within North America.

Then let’s say that you also wanted to drill down into two specific regions. You could set the region filter to Great Lakes and South Central, and now you have a list of those opportunities. But more importantly, within the filter panel, you can roll up the total opportunity count, by group, by region. So you would see Great Lakes bolded, and then you would see all the opportunities but you would also see at that regional level the total pipeline including whatever totals or subtotals you would want to roll up. And the same for South Central.

If you find information that is outdated, you can make quick changes, without opening the actual account record because you are using the Filter Panel on an EditAble CRM Grid.

The examples above are from a pre-built set of Filter Panels in the AbleBridge Sales Pipeline Management tool.

But Filter Panels can be applied to anything in Dynamics CRM, not just sales data. You can analyze any scenario imaginable.

For example, you can apply Filter Panels to manufacturing parts. You can change parameters to find out find out how much inventory you have on hand, sorted by specific types and locations.

Filter Panels are completely interactive. Anytime any parameters change, the data on the screen is updated accordingly. And all the data analysis stays within Microsoft Dynamics CRM, so the information is always up to date and available to all Dynamics CRM users.

Leave the Excel spreadsheets for the Finance team, and do your analysis within Microsoft Dynamics CRM with Filter Panels.

For more information on adding Filter Panels in your Microsoft Dynamics CRM contact AbleBridge, 877-600-2253 or [email protected].

By AbleBridge, Massachusetts Microsoft Dynamics CRM Partner, www.ablebridge.com

Twitter: @AbleBridge

*This entry passed through the Full-Text RSS service – if this is your content and you’re reading it on someone else’s site, please read the FAQ at fivefilters.org/content-only/faq.php#publishers.*