Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.
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?
Solved! Go to Solution.
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 🙂
Check out the November 2023 Power BI update to learn about new features.
Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.
Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!