How To Remove Filter From Power BI DAX

I am working on a report for an organization, and I faced a requirement to identify and handle blank values in Power BI reports. Several columns contained empty data, which we did not want to consider in reports.

Power BI provides multiple DAX functions, such as ISBLANK, COALESCE, FIRSTNONBLANK, and LASTNONBLANK, to identify, remove, or replace blank values from the dataset.

Using these DAX functions, we can easily exclude blank values and work only with valid data in Power BI reports.

In this Power BI tutorial, I will explain how to filter blank and non-blank values using different DAX functions. You will learn how to use ISBLANK, COALESCE, FIRSTNONBLANK, and LASTNONBLANK to remove empty values and work only with valid data in your Power BI reports.

Filter Blank Values using ISBLANK in Power BI

The ISBLANK function in Power BI DAX is used to check whether a column value is blank or not. It returns TRUE if the value is blank and FALSE if the value contains data.

Syntax:

ISBLANK(<value>)

In this example, I am using a Sales Data table that contains some blank values in the Sales Amount column.

I want to filter out rows where the Sales Amount is blank and work only with valid data.

To do this, follow the steps below:

1. Open Power BI Desktop and load your dataset. In my case, I loaded the dataset shown above.
2. Go to the Table view from the left navigation pane and select the required table.

3. Under Table tools, click New column.

4. Now, enter the DAX expression below to filter blank values using the ISBLANK function:

Is Sales Blank = ISBLANK('Sales'[Sales Amount])

Where:

• Sales: Table Name
• Sales Amount: Column Name

After creating the column, Power BI adds a new column that returns TRUE for blank Sales Amount values and FALSE for non-blank values.

This way, you can use the ISBLANK function to Filter Blank Values in Power BI Desktop.

Filter Blank Values using COALESCE in Power BI

The COALESCE function in Power BI DAX is used to return the first non-blank value from a list of expressions. If a column contains blank values, COALESCE helps replace those blanks with a default value.

Syntax:

COALESCE(<expression1>, <expression2>, ...)

In this example, I will use the same dataset and check the Sales Amount column. If the Sales Amount is blank, I will replace it with 0.

Follow the steps below:

1. Open Power BI Desktop and load the dataset. Go to the Table view and select the table.
2. Under Table tools, click New column.

3. Now enter the DAX expression below and click Enter:

Sales Amount (No Blank) = COALESCE('Sales'[Sales Amount], 0)

After applying this formula, Power BI creates a new column where Blank values are replaced with zero, and Non-blank values remain unchanged.

This way, you can work only with valid values and avoid blank-related issues in calculations and visuals.

Filter Blank Values using FIRSTNONBLANK in Power BI

The FIRSTNONBLANK function returns the first non-blank value in a column, based on the evaluation of another column or expression.

Syntax:

FIRSTNONBLANK(<column>, <expression>)

• column – The column you want to evaluate.