Forum Discussion

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

Hi  MadisMerk,

 

Can you explain a bit more?Based on the data you provided,the transport Region which is not attached to them should be the result I get,Company E F G H I doesnt have transport Region attached to them,am I missing something?

 

 

Best Regards,
Kelly

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

MadisMerk's avatar
MadisMerk
Helper I
5 years ago

Hi,

 

I will try. In the company table, you have companies with ID-s from 1 to 9. In the Transport Region table, you have Company ID-s 1,4,5,6,7,8. So in the Transport region Company, ID-s 2,3 and 9 are missing. My purpose here is to be able to use a slicer for filtering based on transport region.

 

The connector between the tables is the ID. It is the primary key in the Customers table and the foreign key in the Transport Regions table.

 

 

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

    Hi  MadisMerk ,

     

    Modify the measure as below:

    Measure = 
var _tab=CALCULATETABLE(VALUES('slicer table'[Transport Region]),ALLSELECTED('slicer table'))
var _tabforcompany=CALCULATETABLE(VALUES('Table (2)'[ID]),'Table (2)'[Transport Region] IN _tab)
VAR _tabl=CALCULATETABLE(VALUES('Table'[ID]),FILTER(ALL('Table'),NOT(MAX('Table'[ID]) in FILTERS('Table (2)'[ID]))))
Return 
IF(SELECTEDVALUE('slicer table'[Transport Region])=BLANK(),
IF(MAX('Table'[ID]) in _tabl,1,BLANK()),
    IF(MAX('Table'[ID]) in _tabforcompany,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
    Helper I
    5 years ago

    Hi,


    The blank part is now working as it should be:

    But the rest is not. One example, transport region D is only attached to company H (ID=8). But when I filter I get a list that does not contain only one company:

    So in my eyes, it does not work.