Filter by items without data

[Pini]

I have a problem that is driving me crazy. I have a matrix-type visual object made up of columns from different related tables. I have the Show element without data option activated. I need to be able to filter on a column to select all the rows that do not contain data in that column. With the filter not white it does not select anything. Any trick to do this?

[Kedar_Pande · Accepted solution]

@Pini 

You can create a DAX measure

IsBlankColumn = 
IF (
ISBLANK ( MAX ( 'Table'[YourColumn] ) ),
TRUE(),
FALSE()
)

Add this IsBlankColumn measure to the Filters on this visual section in Power BI.
Set the filter to show only the TRUE values. This will filter the matrix to show only the rows where the specified column is blank.

💌 If this helped, a Kudos 👍 or Solution mark would be great! 🎉
Cheers,
Kedar
Connect on LinkedIn

[Anonymous · Accepted solution]

Thanks for Kedar_Pande and samratpbi's concern about this issue.

Hi, @Pini 

Since you didn't provide a dataset to perform the test, I created a simple one myself:

As Kedar_Pande's said, you can create a Measure to mark whether a particular row of the column is null.

Also you can create a Calculated column to mark if a row of that column is null:

IsEmptyColumn = IF(ISBLANK([Quantity]), 1, 0)

Then drag the Calculated column to ‘Filters on this visual’ and set it to 1. This will filter out the data where Quantity is null:

I have attached the pbix file for this simple example below, hope it helps.

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Fen Ling,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Kedar_Pande · Accepted solution]

@Pini 

You can create a DAX measure

IsBlankColumn = 
IF (
ISBLANK ( MAX ( 'Table'[YourColumn] ) ),
TRUE(),
FALSE()
)

Add this IsBlankColumn measure to the Filters on this visual section in Power BI.
Set the filter to show only the TRUE values. This will filter the matrix to show only the rows where the specified column is blank.

💌 If this helped, a Kudos 👍 or Solution mark would be great! 🎉
Cheers,
Kedar
Connect on LinkedIn

[Pini]

When I create the measure and apply the filter it doesn't give me the option to filter anything. It doesn't give me the option to choose 1 or 0. It is as if there were no data in that column but in reality there is.

[Pini]

When I create the measure and apply the filter it doesn't give me the option to filter anything. It doesn't give me the option to choose true or false. It is as if there were no data in that column but in reality there is.

[samratpbi]

Hi,

If I understand your problem correctly, you want to filter by a value that does not appear now but may appear in the future and you want keep filter for that in filter pane.

For that, I created a simple table.

Then put those into table visual and apply filter in filter pane on category A,C. Now lets consider I want to keep provision for a future category E which is not available now. 

Hence go to Filter pane -> By default its Basic filtering.

. Select A,C and also select any other random Category, lets say, I select D.

Then I change the Basic Filtering -> Advanced Filtering and change D to E (future value) and apply.

Now Once you will have new values containing E, it will be shown into the report. To demonstrate, I have added Categories E,F. Now Category E is appearing in the visual.

Hope this helps to resolve your problem. If it does, then please accept this as solution provided, thanks!