cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
SuchCT
Helper I
Helper I

Creating an inverse filter

Hello,

I currently have one table with an "Order ID" that can have one or more "Item" inside,each item has an "Item Type" . I currently have a filter for "Item Type".

I'm trying to get a second tablethat shows me all the "Order ID" that don't have the selected "Item Type" inside.

Thanks in advance.

You can see an example of the desired result below.

Full Table
Order ID                |Item                   |Item Type

3832                        1                            A             
69381D
69382C
69383C
80501C
75191A
75191A
76561C
76562D
76563A
76564D


Table when "Item Type" filter is set to "A". Showing all items with that "Filter Type"
Order ID                 |Item                  |Item Type

3832                          1                          A               
75191A
75191A
76563A


Inverse Table when "Item Type" filter is set to "A". Showing all "Order ID" that don't contain "A"
Order ID

6938
8050

 

1 ACCEPTED SOLUTION
v-polly-msft
Community Support
Community Support

Hi @SuchCT ,

Please create a new table that has no relationship with the fact table and use this table as slicer.

 

Table_re = DISTINCT('full table'[item type])

 

111.PNG

Then create a measure as below and add it to visual filter.

 

check = 
IF(SELECTEDVALUE('full table'[Order ID]) in CALCULATETABLE(VALUES('full table'[Order ID]),FILTER(ALL('full table'),'full table'[item type]=SELECTEDVALUE(Table_re[item type]))),1,0)

 

 

222.PNG

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

 

Best Regards

Community Support Team _ Polly

 

 

 

View solution in original post

3 REPLIES 3
v-polly-msft
Community Support
Community Support

Hi @SuchCT ,

Please create a new table that has no relationship with the fact table and use this table as slicer.

 

Table_re = DISTINCT('full table'[item type])

 

111.PNG

Then create a measure as below and add it to visual filter.

 

check = 
IF(SELECTEDVALUE('full table'[Order ID]) in CALCULATETABLE(VALUES('full table'[Order ID]),FILTER(ALL('full table'),'full table'[item type]=SELECTEDVALUE(Table_re[item type]))),1,0)

 

 

222.PNG

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

 

Best Regards

Community Support Team _ Polly

 

 

 

View solution in original post

Thanks for your answer! 
I think this would only show me the second table, where i see the items that don't contain A.

SuchCT_0-1632490165163.png

What I'd be missing here is the first table showing the items that do contain A selected with that same filter.

SuchCT_1-1632490237630.png



This is an image of the desired page. One filter for item type, and two visuals that change based on what was selected on that filter

SuchCT_2-1632490487835.png



Thanks once again

Hi @SuchCT ,

 

Create another new table and refer to the following steps.

Table = ALL('full table')

then manage the relationship.

2222.PNG

 

44444.PNG

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

 

Best Regards

Community Support Team _ Polly

 

 

 

Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Top Solution Authors
Top Kudoed Authors