Negative filters

[infopek]

Hi

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

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.

 

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?

[Eric_Zhang · Accepted solution]

@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]
)

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

See more details from attached pbix.

[infopek]

Thank you! That was very impressive 🙂