single slicer on lookup columns

[chadkoziel]

This problem seems simple, but I'm struggling to find a robust solution.  I'm modelling person-to-person transactions, where a person can be either a sender or a receiver.  I need a single visual slicer that will return transactions where either the sender OR the receiver belonged to a given region.

I've got two tables:

• Transaction ( Transaction.ID, Sender.ID, Receiver.ID, Transaction.Date, others )
• Person ( Person.ID, Region, others )

My previous solution was:

1. Load Transaction table;
2. Join the Region on the Transaction table for the sender ( Sender.Region );
3. Calculate a "Report.Region" column = Sender.Region;
4. Load a second Transaction table;
5. Join Region on the Transaction table for the receiver ( Receiver.Region );
6. Calculate a "Report.Region" column = Receiver.Region;
7. Append the two tables;
8. Use Report.Region as filter;
9. Make judicious use of summarize by distinct Transaction.ID in measures and visuals

That solution is falling apart as I attempt to create measures that use both tables (i.e. filter on Region, create stacked column chart that a) count distinct Sender.IDs that appear in Transaction Table, and b) Person.IDs in Person table that don't appear in Transaction Table).

Any suggestions?

[v-chuncz-msft · Accepted solution]

@chadkoziel,

You may refer to the following post.

https://community.powerbi.com/t5/Desktop/Use-slicer-to-a-filter-table-based-on-values-in-multiple-co...