Forum Discussion

Helper I

5 years ago

Filter on data that does not exist in a table

Hi, I have two tables in my model: ID Name 1 Company A 2 Company B 3 Company C 4 Company D 5 Company E 6 Company F 7 Company G 8 Company H 9 Company I ...

Tips and Tricks

Community Support

5 years ago

Create a slicer table as below:

slicer table = VALUES('Table (2)'[Transport Region])

Then create a measure as below:

Measure = 
var _tab=CALCULATETABLE(VALUES('slicer table'[Transport Region]),ALLSELECTED('slicer table'))
Return
IF(MAX('Table'[_region]) in _tab,BLANK(),1)

And you will see:

For the related .pbix file,pls see attached.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

Helper I

5 years ago

Hi,

Would it also be possible to have a "blank" option in the slicer?

Madis

v-kelly-msft
Community Support
5 years ago
Hi MadisMerk ,

Yes,you could create a slicer table as below:

slicer table = UNION(ROW("Transport Region",BLANK()),VALUES('Table (2)'[Transport Region]))

And you will see:

For the related .pbix file,pls see attached.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!
- MadisMerk
  Helper I
  5 years ago
  Hi,
  
  Yes, but the table is not actually filtered. I will try to explain a bit more how it should work.
  When you filter on blanks it should show only the companies that do not have Transport Regions attached to them. Like this:
  
  As companies with ID-s 2,3,9 do not have any transport region attached to them according to the data provided.
  - v-kelly-msft
    Community Support
    5 years ago
    Hi MadisMerk ,
    
    Create a measure as below:
    
    Measure = var _tab=CALCULATETABLE(VALUES('slicer table'[Transport Region]),ALLSELECTED('slicer table')) VAR _tabl=CALCULATETABLE(VALUES('Table'[_region]),FILTER(ALL('Table'),NOT(MAX('Table'[_region]) in FILTERS('Table (2)'[Transport Region])))) Return IF(SELECTEDVALUE('slicer table'[Transport Region])=BLANK(), IF(MAX('Table'[_region]) in _tabl,1,BLANK()), IF(MAX('Table'[_region]) in _tab,BLANK(),1))
    
    And you will see:
    
    For the related .pbix file,pls see attached.
    
    Best Regards,
    Kelly
    
    Did I answer your question? Mark my post as a solution!

Related content