Filter on data that does not exist in a table

[MadisMerk]

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

ID	Transport Region
1	A
1	B
4	A
4	B
4	C
5	A
6	B
7	C
8	D

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

[amitchandak]

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

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

[MadisMerk]

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]

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]

Hi,

This is the other part of the equation, but how am I able to use it in a slicer?

[v-kelly-msft]

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!

[MadisMerk]

Hi,

Would it also be possible to have a "blank" option in the slicer?

Madis

[v-kelly-msft]

Hi  @MadisMerk ,

Yes,you could create a slicer table as below:

slicer table = UNION(ROW("Transport Region",BLANK()),VALUES('Table (2)'[Transport Region]))

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]

Hi,

Yes, but the table is not actually filtered. I will try to explain a bit more how it should work.

When you filter on blanks it should show only the companies that do not have Transport Regions attached to them. Like this:

As companies with ID-s  2,3,9 do not have any transport region attached to them according to the data provided.

[v-kelly-msft]

Hi @MadisMerk ,

Create a measure as below:

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

[MadisMerk]

Hi,

The printscreen that you sent me is wrong, and also it is wrong in the file 🙂

[v-kelly-msft]

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]

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]

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]

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.