Forum Discussion
Power ParticipantTable - Only show rows where they have empty cells.
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
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
3 Replies
- amitchandak
Super User
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.
StuartSmithSorry, 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-msftCommunity Support
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
