Filter not working "is empty" and "is blank"

[DaniAmo]

I am trying to filter on a report by empty/blank lines.

Column used for a filter come from a related table. Main table (Table A) could have a row or not on the related Table (Table B)

Data Example

I have successfully joined the tables and show all values (Show Items With No Data) and I get this:

What is OK

Now I would like to filter by Blanks/Empties on Column 3, to basically get  lines on table 1 and not table 3.

I have tried with Advance filtering > is blank and is empty and it clean table completlly:

 

I expected to have this:

Column 1	Column 3
E
B

Most estrange thing is that if I Filter by is not blank or is not empty Power bi works fine and return lines with some content on Column 3 (it hide the blank ones…)

Some more info:

- All columns are Text Data type

- I tried with number on Column 3 and it worked fine

- I have ensured there is no any space or hided character on these cells.

- I tried different option on the relation (1 to many, many to many...) did not work

- I also tried with clean and trim the column

NOTE: Screenshot and data are for a dummy file I have created, this issue is coming from a bigger file with real data...

Thanks in advance

[DaniAmo]

Thanks so much for your quick response.

Some comments/questions about your response:

• Data type mismatch: which data type should works with is blank or is empty? I assume text columns should works with both. In fact, if I change data type to numeric, “is empty” option disappear, so I assume power bi only show valid filter options…
• I am not using calculated columns…
• Null handling: I am not doing any replacement or edition of the nulls, it has what Power bi show by default on the join with the two tables.

Said that, for me the key here is that if I filter by is not blank or is not empty it works, and hide the rows properly, the question is why it is not working doing the opposite (filter by is blank or is empty)

Thanks

[saurabhkudale96]

Hello @DaniAmo ,

The "is blank" filter condition in Power BI does not work as expected sometimes because of the way that null or blank values are handled in the data model.

In Power BI, a null or blank value is not the same as a zero or empty string value. A null or blank value indicates that the value is unknown or not applicable, whereas a zero or empty string value indicates that there is no value.

When you use the "is blank" filter condition, Power BI checks for null or blank values in the selected column or measure. However, if the null or blank value is being handled differently in the data model, then the filter condition may not work as expected.

Here are some reasons why the "is blank" filter condition may not work:

Data type mismatch: If the data type of the column or measure is not compatible with the "is blank" filter condition, then the filter may not work as expected. For example, if the column or measure is of a numeric data type, then it may not contain null or blank values, and the filter will not return any results.

Filtering on calculated columns or measures: If you are using calculated columns or measures in your filter, then the "is blank" filter condition may not work as expected. Calculated columns and measures are computed at query time, and they may not contain null or blank values even if the underlying data contains them.

Null value handling: Power BI allows you to configure how null or blank values are handled in the data model. If the null or blank values are being handled differently in the data model, then the "is blank" filter condition may not work as expected. For example, if the null or blank values are being replaced with a default value or a special code, then the filter may not return any results.

To address this issue, you can try the following:

Check the data type of the column or measure that you are filtering on and make sure that it is compatible with the "is blank" filter condition.

Verify that you are not filtering on calculated columns or measures that may not contain null or blank values.

Check the null value handling settings in the data model and make sure that they are consistent with your filter requirements.

Consider using other filter conditions such as "is not null" or "is not blank" to filter out non-null or non-blank values if the "is blank" filter condition is not working as expected.


(Workaround - try replacing blank with "null" and filter out the nulls)

Thanks