How to filter Power BI columns based on Conditions?

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.

2. Select the Employees table.
3. 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

4. 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()
   )

2. Use this measure in visuals to show only active employees.
3. For more complex filtering, create a calculated table:

   Filtered Employees = 
   FILTER(
       Employees,
       Employees[Status] = "Active"
           && Employees[HireDate] <= TODAY()
           && Employees[Salary] > 50000
   )

4. 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.

Filter Power BI data at the layer that owns the rule

Start with a reversible check that separates configuration from missing data. Use Power Query to remove rows permanently during refresh and report filters to change only presentation. Do not combine several repairs at once; a successful result would not reveal which change mattered.

Isolate the failing layer

Write the business condition before translating it into M or DAX. Test nulls, errors, case differences, and boundary values. Use a non-sensitive sample and the normal user account so owner or administrator privileges do not hide the problem.

Compare row counts before and after filtering. Preserve the original state until the new result survives a restart or synchronization cycle.

Verify normal use

Keep an audit sample of excluded records during validation. Check relationships and filter direction when a measure result still looks wrong. Compare the outcome with the expected business workflow, not only a one-time technical test.

Record the application version, account type, device, time, and exact error if behavior still differs. That evidence helps support distinguish a local cache, profile, permission, data, policy, hardware, or service problem. Remove names, addresses, file content, and credentials from screenshots before sharing them.

Have another authorized user repeat the shortest safe test when shared access is involved. Reverse experimental settings that did not help, and keep a note of the final working condition for the next update or device change. Recheck the result after the next sign-in or scheduled cycle when timing can affect it. Keep a concise note of the successful condition so it can be restored later. Verify that the change did not alter unrelated files, permissions, or settings. Recheck the result after the next sign-in or scheduled cycle when timing can affect it. Keep a concise note of the successful condition so it can be restored later.

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.