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
amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
5 years ago

MadisMerk , Use this measure with company name in a table visual and try

 

if(isblank(count(Table2[Transport Region])),1,blank())

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

Hi,

 

It does work partially, but I would like to use it in a slicer and also have the rest of the transport regions present in the same slicer.

 

 

  • 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!