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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.