Power BI Dax Filter [With 15+ Examples]

In this Power Bi tutorial, we will discuss, what is filter function in power bi, and how to use the power bi filter function for reporting. We will see some examples of how to use the Power BI filter function.

For a lot of Power BI reporting in my organization, I use the Power BI filter function. I will share different examples of how I have used the filter function in DAX.

1. An introduction to Power BI DAX filter function and syntax
2. Power BI DAX filter based on the measure
3. Power BI DAX filter multiple values
4. Power BI DAX filter based on the slicer
5. Power BI DAX filter string contains
6. Power BI DAX filter search
7. Power BI DAX filter distinct
8. Power BI DAX filter true false
9. Power BI DAX filter starts with
10. Power BI DAX filter summarize
11. Power BI DAX nested filter
12. Power BI DAX filter selected value
13. Power BI DAX average with filter
14. Power BI DAX groupby filter
15. Power BI DAX filter values
16. Power BI DAX filter by column
17. Power BI DAX filter max value
18. Power BI DAX filter excludes multiple values
19. Power BI DAX filter lookup
20. Power BI DAX Query filter

Power BI DAX filter and syntax

Let us see what the Power BI Dax filter is and its syntax.

• Power Bi DAX filter function returns the filtered value in the table data. And the table contains the value that has been filtered based on the condition applied.

Syntax of DAX Filter :

The below code represents the Power Bi filter function Syntax.

FILTER(<table>,<filter>)

Where,

1. Filter = Function Name
2. Table = Refers to the table name
3. filter = refers to the condition of filtering

This is the Power Bi DAX filter syntax code.

Also Read: Power Query Add Column Date [15 Examples]

Power BI DAX filter based on the measure

Let us see how we can filter the value based on the measure using Power Bi DAX in Power Bi. Check out the Power BI measure.

In this example, I have used the sales table to calculate and filter the value based on the measure.

• Initially, Open the Power Bi desktop and load the table data into it, From the ribbon click on the new measure option and apply the below formula:

M = CALCULATE(SUM(Sales_Table[Sales]),FILTER(Sales_Table,Sales_Table[Product Name]= "Tv"))

Where,

1. M = Measure name
2. Sales Table = Table name
3. Sales, Product Name = Column names

• Now in the report section, from the visualizations select the table visual and the card visual.
• In the table visual, drag and drop the Product name and sales value from the field pane, and in the visual card drag and drop the created Measure value.
• In the below screenshot, you can see that the card visually displays the filtered value based on the condition applied.

This is how to filter the value based on the measure using the Power Bi Dax filter in Power Bi.

Check: Power Query Add Column If Statement

Power BI DAX filter multiple values

Let us see how we can use filter multiple values using the Power Bi Dax filter function in Power Bi.

In this example, we use the sales table to apply multiple values to obtain the desired sum value of sales based on the filter condition.

• Open the Power Bi desktop and load the table data into it, From the ribbon click on the new measure option and apply the below formula:

Multiple Values = CALCULATE(SUM(Sales_Table[Sales]),FILTER(Sales_Table, Sales_Table[Product Name] = "laptop" && Sales_Table[Customer Location]="Alaska"))

Where,

1. Multiple Values = Measure name
2. Sales Table = Table name
3. Sales, Product Name, Customer Location = Column names

• Now in the report section, from the visualizations select the table visual and the card visual.
• In the table visual, drag and drop the Product name, Customer location, and sales value from the field pane, and in the visual card drag and drop the created Measure value.
• In the below screenshot, you can see that the card visually displays the filtered value based on the multiple filters condition applied.

This is how to use filter multiple values using the Power Bi Dax filter function in Power Bi.

Read: How to duplicate multiple columns using Power Query

Power BI DAX filter based on the slicer

Let us see how we can filter the value based on the slicer using the filter function in Power BI DAX.

• In this example, we are going to select two visuals, one is the slicer visual and another one is the card visual.
• So when we select any value from the slicer visual, the sales count value should be displayed in the Card visual
• Open the Power Bi desktop and load the table data into it, From the ribbon click on the new measure option and apply the below formula:

Sales Total = 
VAR selectedValue = SELECTEDVALUE( Sales_Table[Product Name] )
RETURN 
CALCULATE( 
SUM( Sales_Table[Sales] ), 
Sales_Table[Product Name] >= selectedValue
 )

Where,

1. Sales Total = Measure name
2. selected value = Variable name
3. Sales Table = Table name
4. Sales, Product Name = Column names

• Now in the report section, from the visualizations select the slicer visual and the card visual.
• In the slicer visual, drag and drop the Product name column from the field pane, and in the visual card drag and drop the created Measure value.
• In the below screenshot, you can see that the card visually displays the filtered value based on the product name selected in the slicer value called a laptop.

• In the same way, you can see that the below screenshot displays the sales total value for the selected slicer value called smartphone in the card visual.

This is how to filter the value based on the slicer using the filter function in Power BI DAX.

Check: How to add an empty column in Power BI

Power BI DAX filter string contains

Let us see how we can search or filter the string using the Dax filter function in Power Bi,

• In this example, we are going to select two visuals, one is the slicer visual and another one is the card visual.
• So when we select the passed string value from the slicer visual, the string value which we passed in the formula should be displayed in the Card visual else the card visual displays a blank value.
• Open the Power Bi desktop and load the table data into it, From the ribbon click on the new measure option and apply the below formula:

string contains = CALCULATETABLE(VALUES(Sales_Table[Product Name]), 
                FILTER(Sales_Table, CONTAINSSTRING(Sales_Table[Product Name], "laptop")))

Where,

1. string contains = Measure name
2. Sales Table = Table name
3. Sales, Product Name = Column names

• Now in the report section, from the visualizations select the slicer visual and the card visual.
• In the slicer visual, drag and drop the Product name column from the field pane, and in the visual card drag and drop the created Measure value.
• In the below screenshot, you can see that the card visually displays the matched string value based on the product name selected in the slicer value called a laptop.

• In the same way, you can see that the below screenshot displays the blank value in the card visual because the passed string doesn’t match the selected string value.

This is how to search or filter the string using the Dax filter function in Power Bi.

Have a look: How to add a column with a dropdown list in Power Query

Power BI DAX filter search

Let us see how we can search or filter using the Dax filter function in Power Bi.

In this example, we use the search function and filter the value based on the string passed in the search function parameter.

• Open the Power Bi desktop and load the table data into it, click on the new measure option and apply the below formula:

Filter Search = IF(
	ISERROR(
		SEARCH("laptop", Sales_Table[string contains])
	),
	"false",
	"true"
)

Where,

1. Filter Search = Measure name
2. Sales Table = Table name
3. String Contains = Filtered Measure

• Now in the report section, select the table visual from the visualizations.
• In the table visual, drag and drop the Product name column from the field pane, and also drag and drop the created Measure value.
• In the below screenshot, we can see that it displays the true value ( highlighted in red ) for the string value which we passed in the search function.

This is how to search or filter using the Dax filter function in Power Bi.

Power BI DAX filter distinct

Let us see how we can find the distinct count value using the filter function in Power BI,

In this example, I have used the sales table to find the distinct count value of the customer location.

• Initially, open the Power Bi desktop and load the table data into it, From the ribbon click on the new measure option and apply the below formula:

Distinct value = CALCULATE(DISTINCTCOUNT(Sales_Table[Product Name]),FILTER(Sales_Table,Sales_Table[Customer Location]="Florida"))

Where,

1. Distinct value = Measure name
2. Sales Table = Table name
3. Product Name and Customer location = Column names

• Now in the report section, from the visualizations select the table visual and the card visual.
• In the table visual, drag and drop the Product name, sales, and customer location columns from the field pane, and in the card visual drag and drop the created Measure value.
• In the below screenshot, you can see that the card visually displays the distinct count value for the customer location called Florida.

This is how we can find the distinct count value using the filter function in Power BI.

Check post: How to add a column with the same value in Power BI

Power BI DAX filter true false

Let us see how we can use the filter function to check the true or false value based on the condition in Power Bi.

In this example, we use the sales table to apply filters to obtain the result value as true or false based on the filter condition.

• Open the Power Bi desktop and load the table data into it, From the ribbon click on the new measure option and apply the below formula:
• Make sure, the distinct value has been counted by following the above heading.

True/False = if(Sales_Table[Distinct value] < 4 ,"true","false")

Where,

1. True/False = Measure name
2. Sales Table = Table name
3. Distinct value = Calculated Measured Value

• Now in the report section, from the visualizations select the table visual and the card visual.
• In the table visual, drag and drop the Product name, sales, and customer location columns from the field pane, and in the visual card drag and drop the created Measure value.
• In the below screenshot, you can see that the card visually displays the value as true for the customer location called Florida because it matches the distinct count value.
• If it matches with the distinct value it displays true else false.

• In the same way, here you can see that it displays the value as false because it doesn’t match the distinct value.

This is how to use the filter function to check the true or false value based on the applied condition in Power Bi.

Read: How to add column with a fixed value in Power BI

Power BI DAX filter starts with

Let us see how we can apply the Dax filter function to calculate the value starting within Power Bi.

In this example, we will use the sales data to filter the value using the filter function, so it displays the value of the sales count for the string that starts within the filter function.

• Open the Power Bi desktop and load the table data into it, From the ribbon click on the new measure option and apply the below formula:

Filter Startswith = 
SUMX(
FILTER(
'Sales_Table',
Sales_Table[Product Name] = "Tv"
),Sales_Table[Sales])

Where,

1. Filter Starts with = Measure name
2. Sales Table = Table name
3. Product Name and Sales = Column names

• Now in the report section, from the visualizations select the table visual and the card visual.
• In the table visual, drag and drop the Product name, sales, and customer location columns from the field pane, and in the visual card drag and drop the created Measure value.
• In the below screenshot, you can see that the card visually displays the selected Product name called TV and displays the sales count value in the card visual.

• Instead of Product Tv, if we select any other product then the card visual value will be displayed as a Blank value like the below:

This is how to apply the Dax filter function to calculate the value starting within Power Bi.

Power BI DAX filter summarize

Let us see how we can apply the Dax filter function to calculate the value using summarize function in Power Bi.

In this example, we use the summarize function to filter the value based on the need applied.

• Open the Power Bi desktop and load the table data into it, From the ribbon click on the new measure option and apply the below formula:

summarize = SUMMARIZECOLUMNS ( 
    'Sales_Table'[Product Name], 
    FILTER('Sales_Table',Sales_Table[Product Name] = "Desktop") 
)

Where,

1. summarize = Measure name
2. Sales Table = Table name
3. Product Name = Column names

• Now in the report section, from the visualizations select the table visual and the card visual.
• In the table visual, drag and drop the Product name and sales from the field pane, and in the visual card drag and drop the created Measure value.
• In the below screenshot, you can see that the card visually displays the selected Product name called Desktop and displays the sales count value in the card visual.
• If we select any other Product name from the table visual, it displays an error in the card visual.

This is how to apply the Dax filter function to calculate the value using summarize function in Power Bi.

Look: How to add column from another table in Power BI [3 Different ways]

Power BI DAX nested filter

Let us see how we can apply the Dax nested filter function to calculate the value based on the condition in Power Bi.

• Open the Power Bi desktop and load the table data into it, From the ribbon click on the new measure option and apply the below formula:

Nested Filter = 
VAR ProfitValue =
    CALCULATE ( MAX ( Sales_Table[Profit] ), Sales_Table[Product Name] = "Tv" )                                                                RETURN
    CALCULATE (
        SUM ( Sales_Table[Profit] ),
        FILTER ( Sales_Table, Sales_Table[Product Name] = "Tv" && Sales_Table[Profit]= ProfitValue )
    )

Where,

1. Nested Filter= Measure name
2. ProfitValue = Variable Name
3. Sales Table = Table name
4. Product Name and Profit = Column names

• Now in the report section, from the visualizations select the table visual and the card visual.
• In the table visual, drag and drop the Product name and Profit value from the field pane, and in the visual card drag and drop the created Measure value.
• In the below screenshot, you can see that the card visually displays the selected Product name called Tv and displays the sales count value in the card visual.
• If we select any other Product name from the table visual, it displays an error in the card visual.

This is how to apply the Dax nested filter function to calculate the value based on the condition in Power Bi.

Power BI DAX filter selected value

Let us see how we can apply the Dax filter selected function to calculate the value selected in Power Bi.

In this example, we use the selected function to calculate the profit value based on the selection.

• Open the Power Bi desktop and load the table data into it, From the ribbon click on the new measure option and apply the below formula:

Selected Value = 
VAR SELECTED = SELECTEDVALUE(Sales_Table[Product Name])

RETURN 
 CALCULATE(SUM(Sales_Table[Profit]),
    FILTER(Sales_Table ,  
Sales_Table[Product Name] = SELECTED)
          )

Where,

1. Selected Value = Measure name
2. selected = Variable name
3. Sales Table = Table name
4. Sales, Product Name = Column names

• Now in the report section, from the visualizations select the slicer visual and the card visual.
• In the slicer visual, drag and drop the Product name column from the field pane, and in the visual card drag and drop the created Measure value.
• In the below screenshot, you can see that the card visually displays the filtered Profit value based on the product name selected in the slicer value called a Smart Phone,

This is how to apply the Dax filter selected function to calculate the value selected in Power Bi.

Read: Power Query Add Column [35+ Examples in Power BI]

Power BI DAX average with filter

Let us see how we can apply the Dax filter to calculate the average profit value and filter the value based on conditions in Power BI.

In this example, we use the Average function to calculate the profit averaged based on the Product Name called Dish Washer.

• Open the Power Bi desktop and load the table data into it, From the ribbon click on the new measure option and apply the below formula:

Profit Average = CALCULATE(AVERAGE(Sales_Table[Profit]),FILTER(Sales_Table,Sales_Table[Product Name]="Dish washer"))

Where,

1. Profit Average = Measure name
2. Sales Table = Table name
3. Profit, Product Name = Column names

• Now in the report section, from the visualizations select the slicer visual and the card visual.
• In the slicer visual, drag and drop the Product name column from the field pane, and in the visual card drag and drop the created Measure value.
• In the below screenshot, you can see that the card visually displays the calculated average Profit value based on the product name selected in the slicer value called a Dish Washer,

• In the same way, if we select any other products from the slicer visual it displays the blank value as below:

This is how to apply the Dax filter to calculate the average profit value and filter the value based on conditions in Power BI.

Power BI DAX groupby filter

Let us see how we can apply the Dax filter function to calculate the value using summarize function and group the values in Power Bi,

In this example, we use the summarize function to filter the value based on the need applied and group the values using the customer location.

• Open the Power Bi desktop and load the table data into it, From the ribbon click on the new measure option and apply the below formula:

Groupby filter= SUMMARIZE (
    FILTER ( 'Sales_Table', Sales_Table[Profit] > 500 ), Sales_Table[Customer Location]
)

Where,

1. Group by filter = Measure name
2. Sales Table = Table name
3. Product Name = Column names

• Now in the report section, from the visualizations select the slicer visual and the card visual.
• In the slicer visual, drag and drop the customer location from the field pane, and in the visual card drag and drop the created Measure value.
• In the below screenshot, you can see that the card visually displays the customer location for which the profit value is greater than 500.

• If we select any other customer location from the slicer visual, and the selected location profit value is less than 500 then it displays the blank value.

This is how to apply the Dax filter function to calculate the value using summarize function and group the values in Power Bi.

Check out: How to Filter Power BI Dax Based On Condition

Power BI DAX filter values

Let us see how we can apply the Dax filter function to calculate the value using the Value function in Power Bi,

In this example, we use the value function to filter the value based on the selected value from the slicer visual and display them in the card visual using the Product Name.

• Open the Power Bi desktop and load the table data into it, From the ribbon click on the new measure option and apply the below formula:

Value = CALCULATE(VALUES(Sales_Table[Product Name]), FILTER(Sales_Table, Sales_Table[Product Name] = SELECTEDVALUE(Sales_Table[Product Name])))

Where,

1. Value = Measure name
2. Sales Table = Table name
3. Product Name = Column names

• Now in the report section, from the visualizations select the slicer visual and the card visual.
• In the slicer visual, drag and drop the Product Name from the field pane, and in the visual card drag and drop the created Measure value.
• In the below screenshot, you can see that the card visually displays the Product name value based on the selected value in the slicer visual.

This is how to apply the Dax filter function to calculate the value using the Value function in Power Bi.

Power BI DAX filter by column

Let us see how we can apply the Dax filter function to calculate the value based on the column in Power Bi.

• Open the Power Bi desktop and load the table data into it, From the ribbon click on the new measure option and apply the below formula:

column = CALCULATE (
    SUM ( Sales_Table[Profit] ),
    FILTER ( Sales_Table, ( Sales_Table[Product Name] ) = Sales_Table[Product Name] )
)

Where,

1. column = Measure name
2. Sales Table = Table name
3. Product Name = Column names

• Now in the report section, from the visualizations select the slicer visual and the card visual.
• In the slicer visual, drag and drop the Product Name from the field pane, and in the visual card drag and drop the created Measure value.
• In the below screenshot, you can see that the card visually displays the Profit value based on the Product name selected in the slicer visual.

This is how to apply the Dax filter function to calculate the value based on the column in Power Bi.

Have a look: How to Append Columns in Power Query

Power BI DAX filter max value

Let us see how we can apply the Dax filter function to calculate the maximum value based on the max function condition in Power Bi.

• Open the Power Bi desktop and load the table data into it, From the ribbon click on the new measure option and apply the below formula:

Max Value = CALCULATE(MAX(Sales_Table[Sales]),ALLEXCEPT(Sales_Table,Sales_Table[Customer Location]))

Where,

1. Max Value= Measure name
2. Sales Table = Table name
3. Customer Location = Column name

• Now in the report section, from the visualizations select the card visual.
• In the visual card drag and drop the created Measure value from the field pane.
• In the below screenshot, you can see that the card visually displays the maximum value based on the condition.

This is how to apply the Dax filter function to calculate the maximum value based on the max function condition in Power Bi.

Power BI DAX Query filter

Let us see how we can apply the Dax filter function to calculate the value based on the filter function condition in Power Bi.

• Open the Power Bi desktop and load the table data into it, From the ribbon click on the new measure option and apply the below formula:

Filter Rows = CALCULATE(SUM(Sales_Table[Sales]),FILTER(Sales_Table,Sales_Table[Product Name]= "Washing Machine"))

Where,

1. Filter Rows = Measure name
2. Sales Table = Table name
3. Sales, Product name = Column names

• Now in the report section, from the visualizations select the table visual and the card visual.
• In the table visual, drag and drop the Product name and sales value from the field pane, and in the visual card drag and drop the created Measure value.
• In the below screenshot, you can see that the card visually displays the filtered value based on the condition applied.

This is how to apply the Dax filter function to calculate the value based on the filter function condition in Power Bi.

Furthermore, you may like some more Power BI tutorials:

• Power BI DAX Filter Table [With Real Examples]
• How To Remove Filter From Power BI DAX
• How to Filter Blank Value in Power BI
• Power BI DAX Filter If [With Real Examples]
• How to Filter Date using Power BI DAX
• How to merge columns in Power Query
• How to Merge Column in Power BI
• Power BI Slicer Multiple Columns
• Power BI Create Table From Another Table

This Power BI tutorial explains how to work with the Power BI Dax filter function, Power BI Dax filter based on the measure. Also, we discussed some more topics below:

1. Power BI DAX filter multiple values
2. Power BI DAX filter based on the slicer
3. Power BI DAX filter string contains
4. Power BI DAX filter search
5. Power BI DAX filter distinct
6. Power BI DAX filter true false
7. Power BI DAX filter starts with
8. Power BI DAX filter summarize
9. Power BI DAX nested filter
10. Power BI DAX filter selected value
11. Power BI DAX average with filter
12. Power BI DAX groupby filter
13. Power BI DAX filter values
14. Power BI DAX filter by column
15. Power BI DAX filter max value
16. Power BI DAX filter excludes multiple values
17. Power BI DAX filter lookup
18. Power BI DAX Query filter