How to Filter Power BI Dax Based On Condition

Are you familiar with the Power BI DAX filter based on condition filtering options? If not, We will learn everything about the Power BI DAX filter based on conditions in this Power BI tutorial.

Additionally, we will cover a few related headings as mentioned below:

  1. Power BI filter rows based on condition DAX
  2. Power BI filter OR condition
  3. Power BI DAX filter AND condition
  4. Power BI DAX filter contains
  5. Power BI DAX filters all
  6. Power BI DAX filter all except
  7. Power BI DAX count with filter
  8. Filter power bi DAX based on condition by the date
  9. Power bi DAX filter based on condition multiple conditions
  10. power bi Dax filter based on condition true false
  11. power bi if condition in the filter
  12. power bi DAX filter based on condition count
  13. Filter power bi DAX based on condition blank
  14. Power bi DAX filter based on condition based on another column
  15. Power bi Dax filter based on condition calculate
  16. Power bi DAX filter based on condition distinct
  17. Power bi Dax filter based on condition first value
  18. Power bi Dax filter based on condition greater than date

Power BI filter rows based on condition DAX

Let us see how we can filter rows based on conditions using Power BI DAX in Power BI.

In this example, I have used the below-mentioned two tables to filter the rows based on the condition using the Power BI DAX formula.

Table 1:

Power BI filter rows based on condition DAX
Power BI filter rows based on condition DAX

Table 2:

Power BI filter rows based on the condition DAX
Power BI filter rows based on the condition DAX
  • Open the Power BI desktop and load the data into it, Click on the Table Tools tab -> New Table from the ribbon.
  • Then write the below-mentioned Dax Expression in the formula bar and click on the check icon:
Filters rows = 
FILTER ( UNION ( Sciences, Arts ), [Attendances] = 0 )

where,

  1. Filters rows = New Table
  2. Sciences, Arts = Existing Table
  3. Attendances = Column names

In the below screenshot, you can see that it filters the rows based on the condition and displays them in the new table:

Power BI filter rows based on the condition DAX example
Power BI filter rows based on the condition DAX example

This is how to filter rows based on conditions using Power BI DAX in Power BI.

Also Read: How to Append Columns in Power Query

Power BI filter OR condition

Let us see how we can filter rows based on the OR conditions using Power BI DAX in Power BI.

In this example, I have used the below-mentioned Subject table data to filter the rows based on the condition using the Power BI DAX formula.

  • Open the Power BI desktop and load the data into it, Click on the Table Tools tab -> New Column from the ribbon.
  • Then write the below-mentioned Dax Expression in the formula bar and click on the check icon:
FilterCondition = 
IF (
    'Common'[Attendances] = 0 || 'Common'[Subject] = "Physics",
    "Filter",
    "Not Filter"
)

where,

  1. FilterCondition = New Column Name
  2. Common = Existing Table
  3. Attendances,Subject = Existing Column names

In the below screenshot, you can see that it filters the rows based on the condition and displays them in the new calculated column:

Power BI filter OR condition
Power BI filter OR condition

This is how to filter rows based on the OR conditions using Power BI DAX in Power BI.

Check out: How to Filter Date using Power BI DAX

Power BI DAX filter AND condition

Let us see how we can filter rows based on the AND conditions using Power BI DAX in Power BI.

In this example, I have used the below-mentioned Subject table data to filter the rows based on the condition using the Power BI DAX formula.

  • Open the Power BI desktop and load the data into it, Click on the Table Tools tab -> New Column from the ribbon.
  • Then write the below-mentioned Dax Expression in the formula bar and click on the check icon:
FilterCondition = 
IF (
    'Common'[Attendances] = 0 && Common'[Subject] = "Physics",
    "Filter",
    "Not Filter"
)

where,

  1. FilterCondition = New Column Name
  2. Common = Existing Table
  3. Attendances,Subject = Existing Column names

In the below screenshot, you can see that it filters the rows based on the condition and displays them in the new calculated column:

Power BI filter AND condition
Power BI filter AND condition

This is how to filter rows based on the AND conditions using Power BI DAX in Power BI.

Read: How to merge columns in Power Query

Power BI DAX filter contains

Let us see how we can filter rows based on the condition contains using Power BI DAX in Power BI.

In this example, I have used the below-mentioned Subject table data to filter the rows based on the condition using the Power BI DAX formula.

  • Open the Power BI desktop and load the data into it, Click on the Table Tools tab -> New Column from the ribbon.
  • Then write the below-mentioned Dax Expression in the formula bar and click on the check icon:
Filter contains= IF(
	ISERROR(
		SEARCH("Physics", Common[Subjects])
	),
	"false",
	"true"
)

where,

  1. Filter contains = New Column Name
  2. Common = Existing Table
  3. Subjects = Existing Column names
See also  What if Parameter in Power BI

In the below screenshot, you can see that it filters the rows based on the condition matches and displays them in the new calculated column:

Power BI DAX filter contains
Power BI DAX filter contains

This is how to filter rows based on the condition contains using Power BI DAX in Power BI.

Have a look: How to Merge Column in Power BI

Power BI DAX filters all

Let us see how we can filter all functions using Power BI DAX in Power BI.

In this example, I have used the financials table data to filter the rows based on the condition using the Power BI DAX formula.

  • Open the Power BI desktop and load the data into it, Click on the Table Tools tab -> New Measure from the ribbon.
  • Then write the below-mentioned Dax Expression in the formula bar and click on the check icon:
Filter ALL = calculate(sum(financials[ Sales]),Filter(All(financials),financials[Country]="Canada"))

where,

  1. Filter ALL = Measure Name
  2. financials = Existing Table
  3. Sales, Country= Existing Column names
  • In the below screenshot, you can see that it filters the rows based on the condition matches and displays the data value:
  • Select the Card visual and drag and drop the created measure value as shown below:
Power BI DAX filters all
Power BI DAX filters all

This is how to use all functions to filter the data using Power BI DAX in Power BI.

Read out: Power BI Slicer Multiple Columns

Power BI DAX filter all except

Let us see how we can filter the data using filter all except function the Power BI DAX in Power BI.

In this example, I have used the financials table data to filter the rows based on the condition using the Power BI DAX formula.

  • Open the Power BI desktop and load the data into it, Click on the Table Tools tab -> New Measure from the ribbon.
  • Then write the below-mentioned Dax Expression in the formula bar and click on the check icon:
Filter ALLExcept = CALCULATE(SUM(financials[ Sales]),FILTER(ALLEXCEPT(financials,financials[Product]),financials[Country]="Mexico"))

where,

  1. Filter ALLExcept = Measure Name
  2. financials = Existing Table
  3. Sales, Product, Country= Existing Column names
  • In the below screenshot, you can see that it filters the rows based on the condition matches and displays the data value.
  • Select the Card visual and drag and drop the newly created measure value as shown below:
Power BI DAX filter all except
Power BI DAX filter all except

This is how to filter the data using filter all except function the Power BI DAX in Power BI.

Check: Power BI Create Table From Another Table

Power BI DAX count with filter

Let us see how we can use the count with filter function using the Power BI Dax function in Power BI.

In this example, I have used the below-mentioned Subject table data to filter the rows based on the condition using the Power BI DAX formula.

  • Open the Power BI desktop and load the data into it, Click on the Table Tools tab -> New Column from the ribbon.
  • Then write the below-mentioned Dax Expression in the formula bar and click on the check icon:
Count = COUNTROWS(FILTER(Common,Common[Attendances]=0))

where,

  1. Count = Measure name
  2. Common = Existing Table
  3. Attendances = Existing Column names
  • In the below screenshot, you can see that it filters the rows based on the condition and displays the data value.
  • Select the Table visual and drag and drop the newly created measure value as shown below:
Power BI DAX count with filter
Power BI DAX count with filter

This is how to use the count with filter count function using the Power BI Dax function in Power BI.

Filter power bi DAX based on condition by the date

Let us see how we can filter based on conditions by the data using the Power BI Dax formula in Power BI.

In this example, I have used the two mentioned below sample table data. In Table 1 (Value Table) Date and Value columns are presented, and in Table 2 (Date Table) only the Date column has been separated.

Table 1: (Value Table)

Filter power bi DAX based on condition by the date
Filter power bi DAX based on condition by the date

Table 2: (Date Table)

power bi DAX based on condition by the date
power bi DAX based on condition by the date
  • Open the Power BI desktop and load these two tables’ data into it, Click on the Table Tools tab -> New Measure from the ribbon.
  • Then write the below-mentioned Dax Expression in the formula bar and click on the check icon, which calculates the Sum of the Values presented in the Values Table, and also displays the date before two dates based on the condition applied.
Date Filter= CALCULATE(SUM('Value'[value]),FILTER('Value','Value'[Date]>=MAX('Date'[Date])-2 &&'Value'[Date]<=MAX('Date'[Date])))

where,

  1. Date Filter = Measure name
  2. Value,Date = Existing Table
  3. value, Date= Existing Column names
  • In the below screenshot, you can see that it filters the rows based on the condition and displays the sum value based on the selected date filter.
  • Select the Table visual and drag and drop the newly created measure value as shown below:
Filter power bi DAX based on condition by the date example
Filter power bi DAX based on condition by the date example

This is how to filter based on conditions by the data using the Power BI Dax formula in Power BI.

Also read: Power BI filter between two dates [With 15+ Examples]

Power bi DAX filter based on condition multiple conditions

Let us see how we can filter the data based on multiple conditions using the Power BI Dax formula in Power BI.

In this example, I have used the financials table to sum the sales value based on the Multiple countries using multiple conditions, based on condition and then we will add those values (i.e. from Germany and Canada country)”

  • Open the Power BI desktop and load these two tables’ data into it, Click on the Table Tools tab -> New Measure from the ribbon.
  • Then write the below-mentioned Dax Expression in the formula bar and click on the check icon.
Sum of Sales = CALCULATE(
SUM(financials[ Sales]),ALL(financials[ Sales]),
financials[Country] in {"Germany","Canada"}
)

where,

  1. Sum of Sales = Measure Name
  2. financials = Existing Table
  3. Sales, Country= Existing Column names
  • In the below screenshot, you can see that it filters the rows based on the condition matches and displays the data value.
  • Select the Card visual and drag and drop the newly created measure value as shown below:
Power bi DAX filter based on condition multiple conditions
Power bi DAX filter based on condition multiple conditions

This is how to filter the data based on multiple conditions using the Power BI Dax formula in Power BI.

See also  How to Append Columns in Power Query

Power bi Dax filter based on condition true false

Let us see how we can filter rows based on the condition contains using Power BI DAX in Power BI.

In this example, I have used the below-mentioned Employee table data to filter the rows based on the condition using the Power BI DAX formula.

Power bi Dax filter based on condition true false
Power bi Dax filter based on condition true false
  • Open the Power BI desktop and load the data into it, Click on the Table Tools tab -> New Column from the ribbon.
  • Then write the below-mentioned Dax Expression in the formula bar and click on the check icon:
True/False = 
IF ( ISBLANK(Employees[Employee Id]),
    "True",
    "False"
)

where,

  1. True/False = New Column Name
  2. Employees = Existing Table
  3. Employee Id = Existing Column names
  • In the below screenshot, you can see that it filters the rows based on the condition matches and displays the true or false as a result value.
Power bi Dax filter based on condition true false example
Power bi Dax filter based on condition true false example

This is how to filter rows based on the condition true or False using Power BI DAX in Power BI.

Check out: Power BI Sum Group by

Power BI if condition in the filter

Let us see how we can filter using the if condition using Power BI DAX in Power BI.

  • Open the Power BI desktop and load the data into it, Click on the Table Tools tab -> New Column from the ribbon.
  • Then write the below-mentioned Dax Expression in the formula bar and click on the check icon:
Filter IF = IF(
	ISERROR(
		SEARCH("Physics", Sciences[Subjects])
	),
	"false",
	"true"
)

where,

  1. Filter contains = New Column Name
  2. Sciences= Existing Table
  3. Subjects = Existing Column names
  • In the below screenshot, you can see that it filters the rows based on the condition matches and displays them in the new calculated column:
Power BI if condition in the filter
Power BI if condition in the filter

This is how to filter using if condition using Power BI DAX in Power BI.

Power bi DAX  filter based on condition count

Let us see how we can filter using the Power BI DAX Count function in Power BI.

In this example, I have used Arts table data, to count the subjects using the Power BI count function.

  • Open the Power BI desktop and load the data into it, Click on the Table Tools tab -> New Measure from the ribbon.
  • Then write the below-mentioned Dax Expression in the formula bar and click on the check icon:
Count = Calculate(count([Attendances]),filter(Arts, Arts[Subjects] = "Music"))

where,

  1. Count = New Measure Name
  2. Arts= Existing Table
  3. Attendances ,Subjects= Existing Column names
  • In the below screenshot, you can see that it filters the rows based on the condition matches and displays the data value.
  • Select the Table visual from the visualization, drag, and drop created measure in the Table visual.
Power bi DAX  filter based on condition count
Power bi DAX  filter based on condition count

This is how to filter based on conditions using the Power BI DAX Count function in Power BI.

Read: Power BI Compares Two Columns in Different Tables

Filter power bi DAX based on condition blank

Let us see how we can filter Power Bi DAX based on condition blank using the Power BI Dax formula in Power Bi.

In this example, I have used the Employees table data to filter the null values data using the Power Bi Blank function.

  • Open the Power BI desktop and load the data into it, Click on the Table Tools tab -> New Measure from the ribbon.
  • Then write the below-mentioned Dax Expression in the formula bar and click on the check icon:
Blank = 
CALCULATE (
    DISTINCTCOUNT ( 'Employees'[Employee Id] ),
    FILTER ( 'Employees', 'Employees'[Employee Id] <> BLANK () )
)

where,

  1. Count = New Measure Name
  2. Employees = Existing Table
  3. Employee Id = Existing Column names
  • In the below screenshot, you can see that it filters the rows based on the condition matches and displays the data value.
  • Select the Card visual from the visualization, drag, and drop created measure in the Card visual.
Filter power bi DAX based on condition blank
Filter power bi DAX based on condition blank

This is how to filter Power Bi DAX based on condition blank using the Power BI Dax formula in Power Bi.

Power bi DAX filter based on condition based on another column

Let us see how we can filter the data value based on the condition, based on the other columns using Power BI DAX in Power BI.

In this example, I have used the financials table data to filter the rows based on the condition using the Power BI DAX formula.

  • Load the data into the Power BI desktop, Click on the Table Tools tab -> New Measure from the ribbon.
  • Then write the below-mentioned Dax Expression in the formula bar and click on the check icon:
Another Column = CALCULATE(SUM(financials[ Sales]),FILTER(ALLEXCEPT(financials,financials[Country]),financials[Product]="VTT"))

where,

  1. Another Column = Measure Name
  2. financials = Existing Table
  3. Sales, ProductCountry= Existing Column names
  • In the below screenshot, you can see that it filters the rows based on the condition matches and displays the data value.
  • Select the Slicer value, and drag and drop the country column in it. so based on the country it displays the Product value (VTT) in the card visual.
  • Select the Card visual and drag and drop the newly created measure value as shown below:
Power bi DAX filter based on condition based on another column
Power bi DAX filter based on condition based on another column
  • In the same way, below screenshot, you can see that I have selected different countries in the slicer, based on the condition it displays the VTT product value for the selected country in the card visual.
Power bi DAX filter based on condition based on another column example
Power bi DAX filter based on condition based on another column example

This is how to filter the data value based on the Power BI condition applied, based on the other columns using Power BI DAX in Power BI.

See also  How to Concatenate Text and Number in Power BI

Power bi Dax filter based on condition calculate

Let us see how we can filter based on the condition using Calculate Power BI DAX calculate function in Power BI,

In this example, I have used the financials table data to filter the rows based on the condition using the Power BI DAX formula.

  • Load the data into the Power BI desktop, Click on the Table Tools tab -> New Measure from the ribbon.
  • Then write the below-mentioned Dax Expression in the formula bar and click on the check icon:
Calculate Sales Amount = calculate(sum(financials[ Sales]),Filter(All(financials),financials[Country]="Mexico"))

where,

  1. Calculate Sales Amount  = Measure Name
  2. financials = Existing Table
  3. Sales, Country= Existing Column names
  • In the below screenshot, you can see that it filters the rows based on the condition matches and displays the data value:
  • Select the Card visual and drag and drop the created measure value as shown below:
Power bi Dax filter based on condition calculate example
Power bi Dax filter based on condition calculate example

This is how to filter based on the condition using Calculate Power BI DAX calculate function in Power BI.

Read How to duplicate multiple columns using Power Query

Power bi DAX filter based on condition distinct

Let us see how we can filter based on the Power BI Dax distinct function in Power BI.

In this example, you can see that there are two duplicate employee names called Haripriya in the below-mentioned table data. Here we will see how we can filter and count the unique values.

power bi Dax filter based on condition unique values
power bi Dax filter based on condition unique values
  • Load the data into the Power BI desktop, Click on the Table Tools tab -> New Measure from the ribbon.
  • Then write the below-mentioned Dax Expression in the formula bar and click on the check icon:
Distinct Count = DISTINCTCOUNT(Employees[Employee Name])

where,

  1. Distinct Count = Measure Name
  2. Employees = Existing Table
  3. Employee Name = Existing Column names
  • In the below screenshot, you can see that it filters the rows based on the condition and displays the data value which is unique:
  • Select the Card visual and drag and drop the created measure value as shown below, so that it displays the unique values count for the selected column.
Power bi DAX filter based on condition distinct
Power bi DAX filter based on condition distinct

This is how to filter based on the Power BI Dax distinct function in Power BI.

Power bi Dax filter based on condition first value

Let us see how we can filter the first value based on the condition using Power BI DAX in Power BI,

In this example, I am going to filter the first value ID 1, so that it filters and displays the data based on the condition.

  • Load the data into the Power BI desktop, Click on the Table Tools tab -> New Column from the ribbon.
  • Then write the below-mentioned Dax Expression in the formula bar and click on the check icon:
ID 1 = 
IF(
    NOT(
        ISEMPTY(
            CALCULATETABLE( 
                Employees,
                ALLEXCEPT( Employees, Employees[Employee Name] ),
                Employees[Employee Id] = "TS001"
            )
        )
    ),
    "True","False"
)

where,

  1. Distinct Count = Measure Name
  2. Employees = Existing Table
  3. Employee Name = Existing Column names
  • In the below screenshot, you can see that it filters the rows based on the condition and displays the data value for the first value:
  • If it matches with the string, it displays the value true, else it displays false as below:
Power bi Dax filter based on condition first value
Power bi Dax filter based on condition first value

This is how we can filter the first value based on the condition using Power BI DAX in Power BI.

Power bi Dax filter based on condition greater than date

Let us see how we can filter value based on the condition using Power BI DAX in Power BI,

In this example, I am going to sum and filter the Employee’s salary value greater than today’s date (ie, Today’s Date is 27-09-2022), so that it filters and displays the data based on the condition.

  • Load the data into the Power BI desktop, Click on the Table Tools tab -> New Measure from the ribbon.
  • Then write the below-mentioned Dax Expression in the formula bar and click on the check icon:
Greater than date = Calculate(sum([Emp Salary]),Filter(All(Employees), [Date of Joining].[Date]>Today()))

where,

  1. Greater than date = Measure Name
  2. Employees = Existing Table
  3. Emp Salary, Date of Joining = Existing Column names
  • In the below screenshot, you can see that it filters the rows based on the condition sum and displays the employee’s salary value :
  • Select the card visual, drag and drop the created measure in the field section as below:
Power bi Dax filter based on condition greater than date
Power bi Dax filter based on condition greater than date

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

Also, you may like the below Power BI tutorials:

This Power BI tutorial explains how to filter the data value based on the requirement using Power BI DAX in Power BI. Also, it covered the below-mentioned headings:

  • Power BI filter rows based on condition DAX
  • Power BI filter OR condition
  • Power BI DAX filter AND condition
  • Power BI DAX filter contains
  • Power BI DAX filters all
  • Power BI DAX filter all except
  • Power BI DAX count with filter
  • Filter power bi DAX based on condition by the date
  • Power bi DAX filter based on condition multiple conditions
  • Power bi Dax filter based on condition true false
  • power bi if condition in the filter
  • power bi DAX filter based on condition count
  • Filter power bi DAX based on condition blank
  • Power bi DAX filter based on condition based on another column
  • Power bi Dax filter based on condition calculate
  • Power bi DAX filter based on condition distinct
  • Power bi Dax filter based on condition first value
  • Power bi Dax filter based on condition greater than date
>