Forum Discussion
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 sure if I have this set up right relationship wise (All Customers table 1 -> * Contracts Sold). ALSO, how would you approach this? I was thinking that creating a measure would filter the tables as I hoped but that didnt work. My other attempt was to put the Contracts Sold[Customer ID] column in the visual level filters to advanced filter "IS NOT" but that didnt work also.
I am sure this is probably a easy solution for you PowerBI ninjas but I am a noob. Any help from this great community is greatly appreciated!
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
5 Replies
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
Anonymous- no problem, happy to help :)