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 ,

 

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.