How to copy a filter dax table into excel

Question

Hello. How can I copy a filtered dax table into excel? 

Answer 1

If your table is not too large you can do this with DAX Query:

• In Excel in the Data tab, click Existing Connections and select Tables
• Choose a (preferably small) table from your model (not the model itself)
• Load this into a new worksheet as a table
• Right click on the table, select Edit DAX
• In the dialog window, select DAX instead of Table, and enter your DAX table expression within a EVALUATE( ... ) statement
• Excel will now load the results of the DAX table in the worksheet
• If you want to apply filters to the table before loading it into Excel, just wrap it in a CALCULATETABLE function to apply filters

Obviously, the table cannot contain more than 1M rows.

Answer 2

Hi AlexMartini,

I test your scenario using Excel 2016 version and get expected result.

First, I create date model in PowerPivot windows like below screenshot.

 
Then select “2013Jan”cell->Right click->filter->filter by selected cell value. It will filter the data and return all the row where Year/month is 2013Jan.

Finally, select the filtered table, Right click->Copy, and paste it to Excel worksheet.

Best Regards,
Angelia