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
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
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!
