How to filter Power BI columns based on Conditions?

by

How can I filter a column in Power BI based on specific conditions? I have an HR dataset and need to create reports showing only active employees or those meeting certain criteria. What’s the best approach using Power Query and DAX?

Conditionally remove column values in Power Query

We’ll explore two powerful methods: Power Query for ETL processes and DAX for dynamic filtering in reports. I’ll use a simple HR scenario to demonstrate both approaches. We’ll use the following data model: Employees (EmployeeID: Integer, Name: Text, Department: Text, HireDate: Date, Salary: Currency, Status: Text)

  1. Open Power Query Editor:
  • Click “Edit Queries” in the Home tab of Power BI Desktop.
  1. Select the Employees table.
  2. Add a custom column:
  • Go to “Add Column” > “Custom Column”.
  • Name it “IsActive”.
  • Use this formula:
if [Status] = "Active" and [HireDate] <= Date.From(DateTime.LocalNow()) then true else false
  1. Now we can filter the table:
  • Click the filter icon on the “IsActive” column.
  • Select “True” to keep only active employees.
  • Close & Apply changes.

Delete column values in Power BI with DAX

  1. Create a measure for active employees:
   Active Employees = 
   CALCULATE(
       COUNTROWS(Employees),
       Employees[Status] = "Active",
       Employees[HireDate] <= TODAY()
   )
  1. Use this measure in visuals to show only active employees.
  2. For more complex filtering, create a calculated table:
   Filtered Employees = 
   FILTER(
       Employees,
       Employees[Status] = "Active"
           && Employees[HireDate] <= TODAY()
           && Employees[Salary] > 50000
   )
  1. Use this calculated table in your reports for pre-filtered data.

Implementation example

Create a dashboard showing department-wise active employee count:

  1. Use the “Active Employees” measure in a card visual.
  2. Create a bar chart with Department on the axis and the “Active Employees” measure as values.
  3. Add a slicer for the HireDate to dynamically filter employees based on tenure.

What to do in case of issues?

  • If Power Query filters are not updating – go ahead and refresh your data.
  • For slow performance with DAX filters: Consider materializing frequently used filters into calculated tables.
  • Blank results in visuals: Check for data type mismatches or incorrect column names in your measures.
  • Inconsistent filter behavior: Ensure filter context is properly managed using CALCULATE or FILTER functions in DAX.