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.

The Most Basic DAX Functions of Power BI

Hello dear reader! In this article we will be talking about the most basic DAX functions of Power BI. We use these functions a lot in our daily lives. And this shows us that we will be using these while preparing reports.

 

Keep calm and think simple 😃 Yeap, the functions I will talk about go like this:

  • Count (COUNT)
  • Sum (SUM)
  • Calculate the average (AVERAGE)
  • Find the maximum value (MAX)
  • Find the minimum value (MIN)

 

All these functions have a very simple structure. We usually use the functions above in measures. The results of measures give a a single scalar value. We are looking for the answers of questions below as a single scalar value.

I got the exemplary data set for application of these functions from kaggle.com. You can reach example datasets here. This data set contains information about video games like name, rank, platform, year, genre, publisher, global sales . We will be getting answers of some questions we ask to the data set with the functions above.

Let’s transfer this data set to Power BI first. For this choose Text/CSV from Get Data options on the Home tab and lead to this data set. When you click OK to get this .csv format file it will recognize the bracket between texts and display the version automatically divided into columns on the Navigation window and we will only need to click Load.

In this stage we will try to get the answer of the questions below from this data set.

1-How many games are there?

Lets’s satisfy our curiosity about this first. We will count how many games there are by using the COUNT function from the basic DAX functions. We can use any column for this action. We usually choose the columns that we know will never be empty for counting. We write the related table and column name inside the COUNT function and execute this action. You can find the related syntax below: 

Game Count= COUNT(vgsales[Name])

By typing this function we find out that we are talking about 16.598 games here.

2-What is the sum of values on the Global_sales column?

When we talk about sum of values on a column another basic DAX function comes into play. We will obtain the sum of values on the GlobalSales column by using the SUM function. We have to choose a column with numeric values to use the function. The column we want the sum of contains numeric value of Decimal Number data type. We do the action by writing the related table and column name into the SUM function. You can find the related syntax below: 

GlobalSales Sum= SUM(vgsales[Global_Sales])

We obtain the sum of values on the GlobalSales column as 8920.44 with this function.

3- What is the average of values on the Global_Sales column?

Let’s find the average for the same column. If we are talking about the average of values on a column, the function we will use is AVERAGE. To use AVERAGE function as well we have to choose a column that contains numeric data type just like in the SUM function. We can obtain the result we want by writing table and column name in AVERAGE function just like the two functions above. You can find the related syntax below: 

GlobalSales Average= AVERAGE(vgsales[Global_Sales])

AVERAGE function gives as the answer as the value of 0.54. The average sale price of the games in this data set is 0.54.

4-What is the maximum of the values on the NA_Sales column?

This time we have another curiosity on another column. What is the maximum sales value on the NA_Sales column? The answer of this question can be provided by the MAX function only. Of course for this function too the related column has to contain numeric value. But this function gives us two syntax options: we can find the maximum value in a column, or can tell which one is bigger of two scalar values. We will request the maximum value in the whole column. The general use of MAX function is like that. You can find the related syntax below: 

Maximum NA_Sales = MAX(vgsales[NA_Sales])

According to the information we obtained with this function, the maximum value on the NA_Sales column is 41.49.

5-What is the minimum of the values on the NA_Sales Column?

This is the last question we will be asking: What is the minimum sales value on the NA_Sales column? We will use the MIN function to get the answer of this question. While it has been built to bring out the minimum value, the syntax is completely same with the MAX function. The general use is to find the minimum value on a column. You can find the related syntax below:

Minimum NA_Sales = MIN(vgsales[NA_Sales])

According to the information we obtained with this function the minimum value on the NA_Sales column is 0.

With this last question we answered all the questions above by using the most basic DAX functions.

 

DAX fonksiyon sonuçları

If you please, you can download the .pbix file in which all these actions are done here. Until next time, bye!

Good game well played.