Table - Only show rows where they have empty cells.

[StuartSmith]

I have been asked to create a report for users to view any dataset records that have missing data. I have tried using filters, but if I try multiple filters, it will only display rows that meet both filter conditions. 

As an example, if I have a table similar to the below and I filter "Floor" as "Blank" and "Users" as "Blank", I want to see "UK" and "Germany", but the filter displays nothing as no row has both rows with "Blank" in "Users" and "Floors".

Country	City	Floors	Users	Other
UK	London		7	N/A
Germany	Berlin	2		N/A
US	Washington	4	9	N/A
UAE	Dubai	5	8	N/A

So the table should show...

Country	City	Floors	Users	Other
UK	London		7	N/A
Germany	Berlin	2		N/A

How can I get all rows showing that have empty cells? plus one other condition, if a column contains a "0", it should also display as"0" is a default value and needs to be updated to an actual value.

Hope that makes sense.

Stu

[amitchandak]

@StuartSmith , Create two measure like this and use with other un summarised columns

Floors = if( not(isblank(sum(table[Floors]))) && not(isblank(sum(table[Users]))), blank(), sum(Table[Floors]))

Users = if( not(isblank(sum(table[Floors]))) && not(isblank(sum(table[Users]))), blank(), sum(Table[Users]))

Please provide your feedback comments and advice for new videos
Tutorial Series Dax Vs SQL Direct Query PBI Tips
Appreciate your Kudos.

[StuartSmith]

Sorry, where i tried to simplify my table example, I forgot to mention that most columns will be string types and therefore your example produces a unable to display visual error due to "Sum" not working with strings.

I will try to adapt your solution, but any futher advise would be great.  

Also, protentially, it could involve 10 columns that need to be checked for blanks, dont know if this will cause issues.

[v-deddai1-msft · Accepted solution]

Hi @StuartSmith ,

You can create a measure for visual level filter

Measure = IF(ISBLANK(MAX('Table'[Floors])) || ISBLANK(MAX('Table'[Users])),1,0)

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,

Dedmon Dai