How To Write Filters Dax Expression In Power Bi

Introduction to Power BI Filter

A filter is a word nosotros use in MS Excel oft to run across only a specific fix of data. In MS-Excel we are all familiar with the drop-down list to choose only items that are required.

Nonetheless, when information technology comes to Power BI we practice have a filter drop-downwardly list but when summarizing the data we need to use DAX function i.e. Filter. For example when we have all the urban center sales if you want to evidence merely one city sales total so we tin can employ FILTER DAX role to get a total of 1 item city.

What Does Filter Function do in Power BI?

FILTER is but the DAX function used to summarize the data with specifies criteria's. As we have told to a higher place when we have all the cities sales if you want to show only ane city sales full and then we can use FILTER DAX part to get the full of one item city.

FILTER is mainly used with Calculate part, in general, to apply any kind of filters to go far criteria based totals. Below is the syntax of the FILTER office in Power BI.

Table: Commencement we need to mention the table name that we are filtering.
Filter Expression: In the filtering table what is the thing we need to filter. In case of filtering individual city sales we demand to select a metropolis column and past putting equal sign we need to enter the city name in double-quotes.

How to Use Filter DAX Part in Power BI?

Let'due south see some of the examples to understand the functionality of the Filter DAX role in Power BI.

Power BI Filter – Example #1

For demonstrating the Filter function consider the below data tabular array that we are going to use. And so you tin download the excel workbook from the below link which is used for this example.

You can download this Power BI Filter Excel Template hither – Ability BI Filter Excel Template

We have two tables "Sales_Table" and "Incentive_Table".

Upload these 2 tables to Power BI Desktop file by downloading the excel workbook.

At present insert Table visual and create a summary like the below one.

This shows each respective total, now imagine a situation where nosotros need to have a sales summary for each urban center for the year "2015". This is where we can include the "FILTER" function to filter only for the year 2015.

Correct-click on the Sales_Table and cull the New Measure selection.

Give name every bit the 2015 Twelvemonth Sales.

Since we need to sum sales values for the year 2015, open the CALCULATE office first.

The first argument of the CALCULATE function is Expression i.due east. what is the adding nosotros need to practise, so we demand to sum sales value column.

So open SUM part and choose the "Sales" column from "Sales_Table".

As of now, this will sum the "Sales" column at present adjacent argument is Filter1 i.e. while doing the sum of sales column what is the filter status nosotros need to apply.

And so open up FILTER function to use filter condition.

Mention the tabular array proper noun for which we are applying the filter. The table we are applying a filter for is Sales_Table so choose the same table name.

Filter Expression that we are applying is for the column Society Date, and then choose this cavalcade.

Since this is a complete date cavalcade we need to cull the Year item from this column. So put a dot and choose the Year particular.

Now apply the filter criteria every bit 2015.

Close two brackets and hit enter key to get the sales value for the year 2015 simply.

Drag and drop this new measure out i.e. 2015 Yr Sales to the table visual to get the year 2015 total for each city.

This will give visualization every bit shown below.

Now imagine a situation where you need to use multiple filters, for an example we take already created filter for the twelvemonth 2015, now let's say nosotros need to have these sales full only for the land "Texas" in this case we can keep the old formula and after applying FILTER put comma to access side by side argument of Summate function.

The side by side pick of the CALCULATE function is Filter ii so for this open up another FILTER function.

Again choose the table as Sales_Table.

Filter Expression that we need to employ for the column is "Country" so choose the "State" cavalcade.

For this column nosotros need only "Texas" land sales total for the yr 2015, so put an equal sign and enter the criteria as Texas.

Now close two brackets and hit enter key to become the total.

Every bit you lot can encounter to a higher place since we accept edited the existing formula nosotros take sales value just for the city "Texas" and for the year "2015".

Power BI Filter – Example #2

Now, look at 1 more than case of using FILTER. Assume nosotros need to summate the incentive corporeality based on the "Country" cavalcade, for each state we have different incentive percentage, so we need to fetch the incentive percentage from another tabular array.

We will utilize the RELATED function to fetch the incentive details. Presume we need to create a measure that calculates the incentive except for the state "Kentucky", Right-click on Sales_Table and choose New Measure and requite the proper name as "Incentive Except Kentucky".

Open the CALCULATE role.

The calculation that we need to perform is we need to multiply the sales value with incentive percentage which is there in the other tabular array, so open SUMX function.

Mention the tabular array name.

An expression is nothing only the kind of calculation that nosotros demand to do, then first, chooseAuction Value column.

Sales Value cavalcade needs to be multiplied with the incentive percentage column from another table, and so open RELATED function.

The Colum Proper noun that we need to choose from Incentive_Table is "Incentive %", then choose the aforementioned.

Close 2 brackets and this will be the stop of the adding, at present open FILTER function.

Mention the tabular array name equally Sales_Table.

Filter Expression volition exist for the state except "Kentucky", so enter the filter criteria as shown below.

Ok, that all shut the brackets and hitting enter primal to go the result.
Elevate and drop this new measure i.e. Incentive Except Kentucky to the tabular array visual to become the incentive values.

This volition give visualization as shown below.

Wow!!! Equally you tin see above nosotros have incentive values for all u.s. except for the state "Kentucky".

NOTE:Power BI Filter file can too exist downloaded from the link below and the final output can be viewed.

You can download this Power BI Filter Template here – Ability BI Filter Template

Things to Remember

In Power BI, the FILTER function is commonly used with the Summate office.
A RELATED function is used to fetch the data from another table if there is a relationship between ii tables.

Recommended Manufactures

This is a guide to Power BI Filter. Here we discuss Ability BI Filter Function which is used to summarize the data with specifies criteria along with a practical instance. You may too look at the post-obit manufactures to learn more –

Use of Power BI IF Statement
Ability BI Calendar (Examples)
Introduction to KPI in Power BI
How to Create Power BI Reports?
Top Features of Power BI for Data Visualization
Larn the Power BI Bubble Chart
Types of Connections bachelor in Power BI