FILTER: NEW GENERATION FUNCTION

THE FILTER FUNCTION

Hello everybody,

In this article, will talk about the abilities of the FILTER function that is one of the New Dynamic Array functions. As you know, new changes have been coming into our lives with the Office 365. We can tell from the new Office apps updates and fixes published in the Office Insider Fast channel that Microsoft cares about developing Excel -its one of the strongest weapons- and that has focused on this product with Teams.

 

I mentioned in the XLOOKUP article that I was going to talk about the FILTER function details in its own article. Now, it is this function’s turn.

 

WHAT DOES IT DO?

Imagine creating Filters in one field or more. You can list all the record quickly without filtering the rest of the data on the screen with the FILTER function. For example: List me all the Murats in the A column. As you know, the VLOOKUP function gives us one result and brings the first data it finds, unfortunately doesn’t bring the others. The FILTER function brings us all the records. Now we can list the data as a whole with this function without using macros of writing array formulas. The use of the function is quite simple, keep reading for the details. 👍🏻

 

SYNTAX

=FILTER(array; include; [if_empty])

There are 3 arguments in the function.
The first 2 are required and the last one is optional.

Now let’s take a look at what these arguments mean, i.e. what the function wants from us and what we will give it.

array

Required

 The array, or range to filter
include

Required

 A Boolean array whose height or width is the same as the array
[if_empty]

Optional

 The value to return i

 

USE OF THE FUNCTION

First, let’s list the data based on one criterion.
Let’s list based on FİRMA(COMPANY) and all the records of that company. Let’s choose bring the data of the company we choose from the FİRMA SEÇ(CHOOSE COMPANY) cell and if you like, let’s send a message like “No Record” if no data is found. For this, when you write no record into the [if_empty] argument; the text will be written into the cell if a data will not be returned.

 

USING MULTIPLE DYNAMIC CRITERIA

Into the insert argument of the function, we can list the results as a whole if there is data that match the criteria you’ve stated in multiple fields. For example, let’s have two criteria and list the data that match these criteria in the COMPANY and PRODUCT fields. For this, we write each of our criteria into parenthesis and add * (star/cross) in between the criteria.

 

We will be talking about the other New Dynamic Array functions in our next articles.
And then we will be able to get things done way easier by using these new functions together. I can’t help but wish that these formulas have had come out 15 years ago. 😀 LONG LIVE THE NEW DYNAMIC ARRAY FUNCTIONS!

See you in other articles, bye. 🙋🏻‍♂️
You can share this article and help a lot of people get informed as well. 👍🏻

Power BI DAX Functions: the FILTER function

Hello dear reader! In this article we will be analyzing a Power BI DAX Function, the FILTER function. It is one of the most frequently used ones. In our previous articles we talked about most frequently used basic functions like this one. You can read the details here.

Let’s talk about the data set we will be using first. The data set that we will import from kaggle.com again contains top 50 songs on Spotify and their details. You can download it here.

The FILTER function allows us to create datasets filtered by the criteria we desire. It gives a table as a result of the function. Therefore it is among the table functions. If we are going to use the FILTER function only, if we are not going to use it in any other function, we have to use it with the New Table command. The syntax of the FILTER function goes like that:

=FILTER( , )

Both of these parameters have to be written, or the function will fail. If we are to explain the filtering a bit more, we can put it like this: True or false statements should be obtained as a result by using mathematical signs like <“, “>”, “=”, “<>”. You can use logical operators (And & Or) for more a complex filtering.

Let’s start with transferring this data set to Power BI. For this we will go Home > Get Data > Text/CSV.  To get the .csv file you click OK and it will recognize the separator between texts and show the version divided into columns in the Navigation window. We go Load here and make it load to Power BI.

We will create 2 new tables with the FILTER function in this data set. For this we click on the New Table command under the Modelling tab.

1-Filtering by Numbers

Out first table is going to be of those songs we just want to dance to! The column that will enable us to run this query is the Dancebility column. We will list the values over 80 on this column. To write this filtering statement we will be using the “>” mathematical operator. Since we will be filtering rows on a column, it is going to be enough for us to write the name of that column. The syntax we will write goes like this: 

Dance over 80 = FILTER(top50, top50[Danceability]>80)

2-Filtering by Text

Our second table is going to be listing the Pop songs. For this we will be using the Genre column. 

Pop_Songs = FILTER(top50,top50[Genre]="pop")

So we filtered by both text and number. If you paid attention, we didn’t put the numbers in double-quote. Double-quote is used for texts only. And never forget that:

Two single quotes don’t make a double-quote!

I mean apostrophe by single quote. Sometimes some people try to do the action by using two apostrophes. Since this statements has a different meaning in computer language, even though it looks write you will face an error as the result of the function. So I recommend you to always be careful about this.

We came to the part where we move the new tables we obtained from these actions to the report. I moved the columns we filtered first, and then put the non-filtered array to the left. You can get the file here if you want.

spotify top 50 report

We analyzed the the Filter Function which is a Power BI DAX Function. We will keep analyzing other functions, various issues, dead-ends, and possibilities in other articles. Take care!

Good game well played.