Forum Discussion
How to filter by value but keep blanks?
We are trying to use a slicer that will return rows with the selected value OR blank.
For example: in the table below, a slicer that targets department and selects "Admin" would need to return the first row and the two rows with blanks. ie. row 1, 3, and 6.
Data:
| Floor | Room | Department |
| 01 | 101 | Admin |
| 01 | 102 | Research |
| 01 | 103 | |
| 02 | 201 | Research |
| 02 | 202 | Support |
| 02 | 203 |
so that's the first question. How should we go about doing that?
Then to take it up a notch. How would we do the same thing but this time, if a use selects "Admin", the first row is return and the blank row that share the same floor is returned? ie. row 1 and 3 but not row 6.
Thanks in advance for all help and guidance!
Hi mtalbottasg
You can create disconnected table :
And a measure for filtering
Test = IF( SELECTEDVALUE('Table'[Department]) = "",1,IF(SELECTEDVALUE('Table'[Department]) in VALUES('slicer table'[Department]),1,0))Then use a slicer table for a slicer :You can filter out the blank if you need :
And create a table from the original table's fields with the test measure as a filter:
Result :
PBIX is attached
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly
1 Reply
- Ritaf1983
Super User
Hi mtalbottasg
You can create disconnected table :
And a measure for filtering
Test = IF( SELECTEDVALUE('Table'[Department]) = "",1,IF(SELECTEDVALUE('Table'[Department]) in VALUES('slicer table'[Department]),1,0))Then use a slicer table for a slicer :You can filter out the blank if you need :
And create a table from the original table's fields with the test measure as a filter:
Result :
PBIX is attached
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly