Identify orders to exclude in one table to filter another in Power BI desktop.

[EuanP007]

Hi I'm hoping someone could provide some advice or give a working solution to a problem I'm having with Power BI.

I need to allow a user to select a Sales Category or multiple sales categories via a Slicer that then will for the identification of all order_IDs for the Sales Category.  I then need to filter the customers Table so that it excludes any of the order_ID's that the slicer selection has identified.

 Below is a small example dataset with the two tables , which replicates the data structure and expected result example

Customers Table with Order_ID, Customer_ID & Customer_Name

Customer_ID	Order_ID	Customer_Name
C34	T234556	Homer Simpson
C87	T984845	Darth Vader
C23	T643968	James T Kirk
C94	T735396	Eleven

Sales table with Order_ID, Sales_Category & Price. 

Order_ID	Sales_Category	Price
T234556	Free Pen	£             -
T234556	Keyboards	£     15.00
T234556	Mouse Pads	£       2.50
T234556	Mice	£     20.00
T984845	Mice	£     10.00
T984845	Mouse Pads	£       5.00
T984845	Screens	£  100.00
T643968	Keyboards	£     50.00
T735396	Free Pen	£             -
T735396	Mouse Pads	£       5.00

Result_Required is an example of the result table required in Power BI

Any order where Free Pens is selected in a slicer needs to be identified and then filtered from the Customers Table. The slicer will be used to select other sales items and multiple sales items, so needs to be dynamic.

Customer_Id	Order_ID	Customer_Name
C87	T984845	Darth Vader
C23	T643968	James T Kirk

Many thanks

[v-agajavelly]

Hi @EuanP007 ,

I hope the response provided helped in resolving the issue. If you still have any questions, please let us know we are happy to address.

Thanks,
Akhil.

[v-agajavelly]

Hi @EuanP007 ,

Circling back again, were you able to try out Ahmed’s solution with the calculated table and Dynamic Filtered Customers? If it didn’t fully resolve the issue, could you share what result you’re seeing so we can help fine-tune it further.

Thanks,
Akhil.

[v-agajavelly]

Hi @EuanP007 ,

Just checking in were you able to test the solution that Ahmed shared with the calculated table and Dynamic Filtered Customers? It should exclude the orders tied to the selected categories and display the remaining customers. Let us know if it worked as expected or if you ran into any issues.

Thanks,
Akhil.

[v-agajavelly]

Hi @EuanP007 ,

Thanks @Ahmed-Elfeel for jumping in with a detailed solution. The approach you’ve shared especially the calculated table with Dynamic Filtered Customers already covers the requirement exactly orders tied to the selected categories get excluded, and the remaining customers are shown.

@EuanP007 could you please give this a try in your report and confirm if it works for you?

Thanks,
Akhil.

[Ahmed-Elfeel · Accepted solution]

Hi @EuanP007,

I hope you are doing well😄

Ok here is the DAX Solution (DAX measures)🚀

At First you should Create a Measure to Identify Orders to Exclude (Step 1)

Orders to Exclude Count = 
CALCULATE(
    DISTINCTCOUNT(Sales[Order_ID]),
    ALLSELECTED(Sales[Sales_Category])
)

 

Create a Measure for Filtered Customers (Step 2)

Filtered Customer Count = 
CALCULATE(
    DISTINCTCOUNT(Customers[Customer_ID]),
    FILTER(
        Customers,
        NOT(Customers[Order_ID] IN 
            CALCULATETABLE(
                VALUES(Sales[Order_ID]),
                ALLSELECTED(Sales[Sales_Category])
            )
        )
    )
)

Create a Dynamic Table using DAX (Step 3)

Dynamic Filtered Customers = 
VAR ExcludedOrders = 
    CALCULATETABLE(
        VALUES(Sales[Order_ID]),
        ALLSELECTED(Sales[Sales_Category])
    )
RETURN
FILTER(
    Customers,
    NOT(Customers[Order_ID] IN ExcludedOrders)
)

Let me know if it worked I have another 2 solution you can do 😀

If this post helps, then I would appreciate a thumbs up and mark it as the solution to help the other members find it more quickly.