Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Filtering Between 2 Tables

Greetings datanauts!    Please see screenshot below.    Goal: I am trying to filter the All Customers table to remove any Customer ID's that are in the Contracts Sold table.  Problem: I am not...
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • ssugar's avatar
    ssugar
    7 years ago

    Using the sample data you provided, I was able to create a PATH of the company ids with contracts sold, and then create a calculated table that filters the all customers table to remove anything in the PATH of the company ids with contracts sold.

     

    DAX measure to create the path:

    Customer Id With Contract Sold = CONCATENATEX('Contracts Sold', 'Contracts Sold'[Customer Id], "|")

     

    DAX calculated table to create a table that only shows customers with no contract sold:

    Customers with No Contract Sold = FILTER('All Customers', PATHCONTAINS([Customer Id With Contract Sold], 'All Customers'[Customer Id]) = FALSE())

     

     

     

    PBIX file with this working: https://github.com/ssugar/PowerBICommunity/raw/master/community-sol-265075.pbix

ssugar's avatar
ssugar
Resolver III
7 years ago

Anonymous- no problem, happy to help :)

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Would you be able to provide additional advice please? Lets say we want to filter by multiple tables and fields...how would you go about that? 

 

EX: We would need to filter for:

- any customer id not in contracts sold table

- which used payment method cash

- and bought cars

 

I have tried your initial suggestion and it worked fantastic! Only problem is that I tried to expand the search criteria to include multiple tables and fields but that made a complete mess. 

 

If you are able to help guide me to a solution, it will be so appreicated! 

 

Gratitude! 

  • ssugar's avatar
    ssugar
    Resolver III
    7 years ago

    Sure :)

     

    New Measure:

    Customer Id With Payment Cash = CONCATENATEX(FILTER('Payment Method', 'Payment Method'[Payment] = "Cash"), 'Payment Method'[Customer Id], "|")

     

    New Measure:

    Customer Id With Product Cars = CONCATENATEX(FILTER('Product Type', 'Product Type'[Product] = "Cars"), 'Product Type'[Customer Id], "|")

     

    New Calculated Table:

    Customers with No Contract Sold With Paid Cash And Bought Cars = FILTER('All Customers', PATHCONTAINS([Customer Id With Contract Sold], 'All Customers'[Customer Id]) = FALSE() && PATHCONTAINS([Customer Id With Payment Cash], 'All Customers'[Customer Id]) = FALSE() && PATHCONTAINS([Customer Id With Product Cars], 'All Customers'[Customer Id]) = FALSE())

     

    Updated pbix file - https://github.com/ssugar/PowerBICommunity/raw/master/community-sol-265075.pbix