Re: Slicer date period filtering based on table filtering

GoldenSandwich — Fri, 24 Nov 2023 10:39:47 GMT

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.

Slicer date period filtering based on table filtering

GoldenSandwich — Wed, 22 Nov 2023 14:27:35 GMT

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.

GoldenSandwich Tracker

Re: Slicer date period filtering based on table filtering

Slicer date period filtering based on table filtering