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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
infopek
Frequent Visitor

Negative filters

 

Hi

Let’s say I have data model that looks like this:

 

negative1.png

 

 

With this I can very easily create a report that shows all orders. I can also add a filter on products so only orders containing a specific product is shown.

 

 negative2.png

 

But can I somehow adding a filter that makes it possible for the user to show all orders that contains product A, but not product B?

1 ACCEPTED SOLUTION
Eric_Zhang
Microsoft Employee
Microsoft Employee

@infopek

AFAIK, there's no such a exclude filter. A workaround I think of is to use a exclude table.

 

exclude Products = 
SELECTCOLUMNS (
    FILTER (
        FILTER (
            CROSSJOIN (
                SELECTCOLUMNS (
                    CROSSJOIN ( Orders, Products ),
                    "OrderID_", Orders[OrderID],
                    "ProductID_", Products[ProductID],
                    "ProductName", Products[Name]
                ),
                SUMMARIZE (
                    OrderLine,
                    OrderLine[OrderID],
                    "ProductIDs", CONCATENATEX ( OrderLine, OrderLine[ProductID], "," )
                )
            ),
            [OrderID_] = OrderLine[OrderID]
        ),
                SEARCH ( [ProductID_], [ProductIDs],, 0 ) = 0
    ),
    "OrderID", [OrderID_],
    "ProductName", [ProductName]
)

Capture.PNG

 

See a demo, the Orders that contains Pasta but not Fish.

 

Capture.PNG

 

See more details from attached pbix.

 

View solution in original post

2 REPLIES 2
Eric_Zhang
Microsoft Employee
Microsoft Employee

@infopek

AFAIK, there's no such a exclude filter. A workaround I think of is to use a exclude table.

 

exclude Products = 
SELECTCOLUMNS (
    FILTER (
        FILTER (
            CROSSJOIN (
                SELECTCOLUMNS (
                    CROSSJOIN ( Orders, Products ),
                    "OrderID_", Orders[OrderID],
                    "ProductID_", Products[ProductID],
                    "ProductName", Products[Name]
                ),
                SUMMARIZE (
                    OrderLine,
                    OrderLine[OrderID],
                    "ProductIDs", CONCATENATEX ( OrderLine, OrderLine[ProductID], "," )
                )
            ),
            [OrderID_] = OrderLine[OrderID]
        ),
                SEARCH ( [ProductID_], [ProductIDs],, 0 ) = 0
    ),
    "OrderID", [OrderID_],
    "ProductName", [ProductName]
)

Capture.PNG

 

See a demo, the Orders that contains Pasta but not Fish.

 

Capture.PNG

 

See more details from attached pbix.

 

Thank you! That was very impressive 🙂

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Feb2025 NL Carousel

Fabric Community Update - February 2025

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