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
MadisMerk's avatar
MadisMerk
Icon for Helper I rankHelper 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
Icon for Community Support rankCommunity 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
    Icon for Helper I rankHelper 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.