Forum Discussion

infopek's avatar
infopek
Frequent Visitor
9 years ago
Solved

Negative filters

 

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?

Need Help
  • Eric_Zhang's avatar
    Eric_Zhang
    9 years ago

    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.

     

2 Replies

  • Eric_Zhang's avatar
    Eric_Zhang
    Microsoft Employee
    9 years ago

    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's avatar
      infopek
      Frequent Visitor
      9 years ago

      Thank you! That was very impressive :)