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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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
Employee
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
Employee
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
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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