How to Filter Blank Value in Power BI

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, LASTNONBLANK, FILTER, and CALCULATE, 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, LASTNONBLANK, FILTER, and CALCULATE to remove empty values and work only with valid data in your Power BI reports.

Power BI DAX filter is not empty

Here we will how we can filter the value using the Power bi Dax filter function.

In this example, we will use the below-mentioned sales table data to find the count of the not empty values using the filter function.

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

Not Empty = 
CALCULATE (
    COUNTROWS( ( Sales_Table)),
    FILTER ( 'Sales_Table', Sales_Table[Discount] <> BLANK () )
)

Where,

1. Not Empty = Measure name
2. Sales Table = Table name
3. Discount = Column name

• Now in the report section, select the table visual and card visual from the visualizations.
• In the table visual, drag and drop the Product name, Discount, and Customer location from the field pane. In the visual card drag and drop the created Measure.
• In the screenshot below, you can see that the card visually displays the not-empty field count values.

This is how to filter the value using the Power bi Dax filter function in Power Bi.

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

Power BI DAX filter does not equal

Let us see how we can filter the value using the Power Bi Dax filter function in Power BI.

Here we will create a filter that calculates the sum according to our expression. Let us create a measure showing the filtered data except for the Product Laptop.

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

Measure = CALCULATE(SUM(Sales_Table[Sales]),FILTER(Sales_Table, Sales_Table[Product Name]<> "Laptop"))

Where,

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

• Now in the report section, select the table visual from the visualizations.
• In the table visual, drag and drop the Product name and sales value from the field pane, and select another table visual drag and drop the created Measure.
• The screenshot below shows that the table containing the measured value filters the data based on the applied condition.

This is how we can filter the value using the Power Bi Dax filter in Power Bi.

Power BI DAX filter isblank

Now we will see how we can filter the value using the blank function with the filter if function in Power BI.

In this example, we will first calculate the blank values count, and then in the card visual, we will display the value as true or false, if the calculated value has a blank value then it should display a true value or else false.

• Open the Power BI desktop and load the data into the Power Bi desktop, Once the data has been loaded click on the new measure option from the ribbon

ISBLANK = IF(ISBLANK(Sales_Table[M]),TRUE(),FALSE())

Where,

1. ISBLANK = Measure name
2. Sales Table = Table name
3. M = Calculated Measure Value

• Now in the report section, select the table visual and card visual from the visualizations.
• In the table visual, drag and drop the Product name, Discount, and Measure value from the field pane. In the card visual drag and drop the created ISBlank measure value.
• In the screenshot below, you can see that the card visually displays the value as False because the calculated measure value is not Blank.

This is how to filter the value using a blank function with the filter function in Power BI.

Check out: Power Query Add Column If Statement

Power BI DAX filter is not blank

Let us see how we can filter the value using a Not blank function with a filter function in Power BI.

In this example, we will first calculate the not blank values count, and then in the card visual, we will display the value as true or false, if the calculated value has a not blank value then it should display a true value, or else false.

• Open the Power BI desktop and load the data into the Power Bi desktop, Once the data has been loaded click on the new measure option from the ribbon

IsNotBlank = NOT(ISBLANK( Sales_Table[M]))

Where,

1. IsNotBlank = Measure name
2. Sales Table = Table name
3. M = Calculated Measure Value

• Now in the report section, select the table visual and card visual from the visualizations.
• In the table visual, drag and drop the Product name, Discount, and Measure value from the field pane. In the card visual drag and drop the created ISNotBlank measure value.
• In the screenshot below, you can see that the card visually displays the value as True because the calculated measure value is not Blank.

This is how to filter the value using a Not blank function with a filter function in Power BI.

Have a look: How to duplicate multiple columns using Power Query

Power BI DAX firstnonblank filter

Let us see how we can filter the value using the first nonblank function with a filter function in Power BI.

In this example, we will first find the first nonblank value and display the sales value in the card visual.

• Open the Power BI desktop and load the data into the Power Bi desktop, Once the data has been loaded click on the new measure option from the ribbon

 FIRSTNONBLANK = 
VAR MYVALUE =
    FIRSTNONBLANK ( Sales_Table[Product Name], 0 )
RETURN
    CALCULATE ( SUM ( 'Sales_Table'[Sales] ), FILTER(Sales_Table,Sales_Table[Product Name]=MYVALUE ))

Where,

1. FIRST NONBLANK Value = Measure name
2. MYVALUE = Variable name
3. Sales Table = Table name
4. Sales, Product Name = column names

• Now in the report section, select the table visual and card visual from the visualizations.
• In the table visual, drag and drop the Product name, Sales value from the field pane. In the card visual drag and drop the created FIRSTNONBLANK Value measure.
• In the screenshot below, you can see that the card visually displays the value as the first non-blank sales value.

This is how to filter the value using a first nonblank function with a filter function in Power BI.

Read: How to add an empty column in Power BI

Power BI DAX filter first value

Let us see how we can filter the first value using all expected functions to filter the first data value in Power BI.

In this example, we will first find the first value and display the Product name in the card visual.

• Open the Power BI desktop and load the data into the Power Bi desktop, Once the data has been loaded click on the new measure option from the ribbon.

First Value = VAR _firstProduct = 
    CALCULATE (FIRSTNONBLANK ( Sales_Table[Product Name], 1),
        ALLEXCEPT ( 'Sales_Table', 'Sales_Table'[Order Date], Sales_Table[Customer Name] ),Sales_Table[Customer Location]="Florida"

    ) return IF ( ISBLANK ( _firstProduct ), "No product found", _firstProduct )

Where,

1. First Value = Measure name
2. first product = Variable name
3. Sales Table = Table name
4. Sales, Product Name, Customer Name, Customer Location= column names

• Now in the report section, select the table visual and card visual from the visualizations.
• In the table visual, drag and drop the Product name, Sales value, and customer location from the field pane. In the card visual drag and drop the created First Value measure.
• In the screenshot below, you can see that the card visually displays the first value product name based on the customer location mentioned.

This is how to filter the first value using all expected functions to filter the first data value in Power BI.

Additionally, 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 add a column with the same value in Power BI
• How to add column with a fixed value in Power BI
• How to add column from another table in Power BI [3 Different ways]
• Power Query Add Column [35+ Examples in Power BI]
• How to Filter Date using Power BI DAX

This is how to filter the blank value using the filter function in Power BI, Also covered the below-mentioned topics in this Power Bi tutorial.

• Power BI DAX filter is not empty
• Power BI DAX filter does not equal
• Power BI DAX filter isblank
• Power BI DAX filter is not blank
• Power BI DAX firstnonblank filter
• Power BI DAX filter first value