How to create filtered datatable from another two not related

[VirgilijusB]

Hi Experts.

I need help to solve one task. I need to create a data table from two, not related tables. I would like to use dynamic filters for source tables and create new one data table that merges filtered rows from the first table and filtered rows from the second table. I hope that the picture better explains the task.

Thank you very much in advance.

VirgisB

[kcantor]

@VirgilijusB 

The most simple solution is to use Power Query to reference both tables you want to combine into new versions, filter to the products you want, change the column name to JointProduct in each referenced and filtered version of the table, make sure the other fields you want to keep have exact same names and append the two filtered, referenced tables together. Then remove all columns you don't need.

[VirgilijusB]

@kcantor 

Thanks for your proposed solution. It could be a solution but I would like to know: is it another solution by using DAX? There is a question about performance as each data table could have more than a million records and create new one data table without filters will have two million records. It will be about 4 million records in total counting all three data tables. 

[kcantor · Accepted solution]

This really should be the best way. In Query Editor, using reference, the data is only pulled once for each original table which would hold true if you were planning to do this in DAX. In addition, once you append, you can choose to not load the unnecessay tables to keep from having a larger file. Finally, Appending into a single table means the append only occurs during refresh not every time the DAX measure is used.  You may experience some sluggishness in Query Editor but this will not transfer over into the DAX engine.