Forum Discussion
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 containi...
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.
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.
Thank you! That was very impressive :)
