Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Compete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.

Reply
MadisMerk
Helper I
Helper I

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

 

14 REPLIES 14
amitchandak
Super User
Super User

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

 

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

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

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.

 

MadisMerk_0-1614349561273.png

 

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:

v-kelly-msft_0-1614672502580.png

 

For the related .pbix file,pls see attached.

 

 

Best Regards,
Kelly

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

 

Hi,

 

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

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:

 

v-kelly-msft_0-1614734541674.pngv-kelly-msft_1-1614734550373.png

 

For the related .pbix file,pls see attached.

 

Best Regards,
Kelly

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

Hi,

 

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

 

Madis

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:

v-kelly-msft_1-1614752512960.png

 

For the related .pbix file,pls see attached.

 

Best Regards,
Kelly

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

 

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:

 

MadisMerk_0-1614754400906.png

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

 

 

 

 

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:

v-kelly-msft_0-1614939241698.png

 

For the related .pbix file,pls see attached.

 

 

 

Best Regards,
Kelly

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

Hi,


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

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!

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.

 

 

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:

v-kelly-msft_0-1615283308141.png

 

For the related .pbix file,pls see attached.

 

Best Regards,
Kelly

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

Hi,


The blank part is now working as it should be:

MadisMerk_0-1615287095151.png

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:

MadisMerk_1-1615287197723.png

So in my eyes, it does not work.

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors