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

Microsoft is giving away 50,000 FREE Microsoft Certification exam vouchers. Get Fabric certified for FREE! Learn more

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
PBIApril_Carousel

Power BI Monthly Update - April 2025

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

Notebook Gallery Carousel1

NEW! Community Notebooks Gallery

Explore and share Fabric Notebooks to boost Power BI insights in the new community notebooks gallery.

April2025 Carousel

Fabric Community Update - April 2025

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

Top Solution Authors