Forum Discussion

MadisMerk's avatar
MadisMerk
Icon for Helper I rankHelper 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 ...
Need Help
Tips and Tricks
v-kelly-msft's avatar
v-kelly-msft
Icon for Community Support rankCommunity Support
5 years ago

Hi MadisMerk ,

 

First create a column in company table:

_region = RIGHT('Table'[Name],1)

Then create a measure as below:

Measure = IF(NOT(MAX('Table'[_region]) in FILTERS('Table (2)'[Transport Region])),1,BLANK())

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's avatar
MadisMerk
Icon for Helper I rankHelper I
5 years ago

Hi,

 

This is the other part of the equation, but how am I able to use it in a slicer?

  • v-kelly-msft's avatar
    v-kelly-msft
    Icon for Community Support rankCommunity Support
    5 years ago

    Hi MadisMerk ,

     

    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!

    • MadisMerk's avatar
      MadisMerk
      Icon for Helper I rankHelper I
      5 years ago

      Hi,

       

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

       

      Madis

      • v-kelly-msft's avatar
        v-kelly-msft
        Icon for Community Support rankCommunity 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!