How to Parametize to a Data Model-to-Excel sheet query?

[cr731]

I have a table in my data model which I have a linked table back into Excel (via Data > Existing Connections > Tables > myTable). However, I only want rows of my table where a certain column equals a specific value (saving me from bringing back about 75% of the table that I don't need).

I see that if I right-click the query table there is an Edit DAX option, which I'm wondering if I can use to apply a filter.

How would I write that DAX statement or is there any other easier way? I want the data returned as a table, not as a PivotTable.

Thanks

 

[MarcelBeug]

The better solution is to use Get & Transform Data (a.k.a. Power Query).
Watch this video for a small demo, in which some filtering is applied to an Excel table and results loaded nack to a new table.

 

[cr731]

The better solution is to use Get & Transform Data (a.k.a. Power Query).
Watch this video for a small demo, in which some filtering is applied to an Excel table and results loaded nack to a new table.

I'm connecting to a SSAS cube and for some reason, when I use Power Query, it takes an extremely long time to refresh (sometimes I leave it running 2+ hours and still doesn't finish), but if I create the same query in PowerPivot, it refreshes in about 15-20 minutes. So, that's why I'm getting the data into PowerPivot, and then I'd like to return it to an Excel table. As far as I know, PQ can't retrieve data from the data model?

Thanks

 

[MarcelBeug]

In that case my solution doesn't make any sense. Apologies for that.
Unfortunately I won't be able to help you with DAX.
I'll try and involve Matt Allington to come to the rescue...

 

[Matt Allington]

Sorry, been busy. Yes you can use the edit dax feature in a table. You need to write a dax formula that returns a table.
Eg

evaluate
calculatetable(Sales,Products[Category]="Bikes")

you can read my article here Getting Started with DAX Studio - Excelerator BI