Slicer date period filtering based on table filtering

[GoldenSandwich]

Hello,

I'm relatively new to PowerBI and can't say I'm fluent yet in DAX as I usually work in SQL and general programming.

I'm not exactly sure about the wording of my issue, but I'll try to explain everything in a simplified context:

I have two tables:

Vendor_Employee_Match (vendor_id, vendor_name, employee_id, employee_name, employee_termination_date) 

Vendor_Transactions (vendor_id, transaction_id, amount, document_date)

Now, these tables are connected in *:* (many-to-many) relationship on vendor_id field, because vendor_id is not unique in any of these tables (sometimes fuzzy match finds multiple persons for a single vendor_name, and, well, transactions). 

The flow is following: User clicks on Vendor_Employee_Match row to limit Vendor_Transactions to only those with matching vendor_id. Now, the user needs to know whether there are transactions that occured (document_date) after employee_termination_date.

I need to create a slicer, that functionally will have an option to either display all matching rows from Vendor_Transactions table, or filter it so only rows from Vendor_Transactions with document_date after employee_termination_date filtered in previous step are shown. 

I've attempted multiple ways to at least filter the slicer in date after.. mode, but I don't know how to assign employee_termination_date as a limiting variable. The fact that I have *:* relationship doesn't make it any easier to me.

How would you approach this? If there will be a need to provide sample data, I will come up with something.

[Anonymous]

Hi @GoldenSandwich ,

I apologize for not understanding what your needs are.
Could you explain in more detail what you mean by "User clicks on Vendor_Employee_Match row to limit Vendor_Transactions to only those with matching vendor_id" and "I don't know how to assign employee_termination_date as a limiting variable"?
I would like you to provide some sample data if possible. I would request that you explain, using example pictures, what the function of the slicer you wish to create is, and what the two sentences mentioned above mean.

I should also remind you that many-to-many relationships can be very limiting, especially if you want to do some calculations or filtering. The general solution to this is to create a bridge table that splits the original many-to-many relationship into two relationships: many-to-one and one-to-many.

Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[GoldenSandwich]

Okay, let me clarify.

This is how the report looks roight now with all data:

Now, the user clicks on chosen entry in top (Vendor_Employee_Match) table. The bottom one (Vendor_Transactions) is now filtered:

Now, the user needs to quickly determine from hundreds of transactions which of these transactions happened after the employee left the company. I'd like to create a slicer with a following function:

As you can see, bottom table was further filtered by a slicer. It doesn't necessarily need to look like this, I've only created a visual representation of how it's supposed to interact.

I don't know if I can provide pbix with report, but this is some sample data I came up with:

Vendor_Employee_Match

vendor_id;vendor_name;employee_id;employee_name;employee_termination_date
0000;Company Alpha;45670;John Smith;20220501
0001;Test Industries;04444;Cassandra Something;20221231
0000;Company Alpha;22222;Paul Jordan;20230425
0002;Gamma Services Inc.;00001;Michael Hat;20200615

Vendor_Transactions

vendor_id;transaction_id;amount;document_date
0002;0;556.18;20180109
0002;1;7999.99;20180524
0001;2;272.40;20190118
0001;3;842.00;20191007
0000;4;9999.94;20191101
0002;5;18.64;20191204
0000;6;542.09;20200305
0001;7;-18.52;20210519
0000;8;37773.00;20231019
0000;9;543.00;20231102
0001;10;43.22;20231104

I hope that my goal is clear now. Thank you for your time.