Create a dynamic filter across two columns, both from different tables

[Anonymous]

Hi all,

I'm trying to create a dynamic filter that operates across two columns in two different tables. To give some context, I want to create a report that allows the user to filter, using a slicer, between the historic taggings of a manger over time, and the current tagging of that manager based on a master site list.

The final output should look something like below:

Current Manager TaggingHistoric Manager Tagging

When the slicer is set to "Current" - the chart will show the revenue over time by manager (from the manager column in the master site list) and when the slicer is set to "Historic" - the chart should show the revenue over time by manager (from the manager column in the revenue table).

I will need to expand this for all the dimensions below (Head of Ops, PMS, Practice Manager and Region), not just the Ops Manager.

See tables below:

In my mind the final revenue table would look like this:

Any assistance on this would be much appreciated!

Thanks,
Aaron

[v-chuncz-msft · Accepted solution]

@Anonymous ,

You may use Append Queries (Table.Combine) in Query Editor or UNION Function (DAX) to create a new table.