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:

 

IDName
1Company A
2Company B
3Company C
4Company D
5Company E
6Company F
7Company G
8Company H
9Company I

 

IDTransport Region
1A
1B
4A
4B
4C
5A
6B
7C
8D

 

The connection between those tables is One to Many (Name to Transport Region). The filter is activated in both directions.

 

The problem that I am facing is that I am not able to filter out Companies that do not have a transport region attached to them. I cannot merge the tables because then I would have multiple lines in the Name table for one Name.

 

Thanks!

 

Madis

 

Need Help
Tips and Tricks

14 Replies

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