Forum Discussion
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 |
| 6938 | 1 | D |
| 6938 | 2 | C |
| 6938 | 3 | C |
| 8050 | 1 | C |
| 7519 | 1 | A |
| 7519 | 1 | A |
| 7656 | 1 | C |
| 7656 | 2 | D |
| 7656 | 3 | A |
| 7656 | 4 | D |
Table when "Item Type" filter is set to "A". Showing all items with that "Filter Type"
Order ID |Item |Item Type
| 3832 | 1 | A |
| 7519 | 1 | A |
| 7519 | 1 | A |
| 7656 | 3 | A |
Inverse Table when "Item Type" filter is set to "A". Showing all "Order ID" that don't contain "A"
Order ID
| 6938 |
| 8050 |
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])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)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
3 Replies
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])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)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
Thanks for your answer!
I think this would only show me the second table, where i see the items that don't contain A.What I'd be missing here is the first table showing the items that do contain A selected with that same filter.
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
Thanks once againHi SuchCT ,
Create another new table and refer to the following steps.
Table = ALL('full table')then manage the relationship.
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
