Use Excel to get data from semantic model using parameters

[jmoedata]

I have a use case where we need to bring data from a PowerBI semantic model into Excel, but would like to only return a subset of data to Excel based on input parameters in Excel that are selected by the user.  Is this possible? I have not been able to figure out how to dynamically limit the data that is returned. (I know that I bring all of the data back and then filter, but do not want all data returned)

Any help is appreciated. Thanks.

[v-pgoloju]

Hi @jmoedata,

Just a gentle reminder — has your issue been resolved? If so, we’d be grateful if you could mark the solution that worked as Accepted Solution, or feel free to share your own if you found a different fix.

This not only closes the loop on your query but also helps others in the community solve similar issues faster.

Thank you for your time and feedback!

Best,

Prasanna Kumar

[v-pgoloju]

Hi @jmoedata,

We wanted to kindly check in to see if everything is working as expected after trying the suggested solution. If there’s anything else we can assist with, please don’t hesitate to ask.

If the issue is resolved, we’d appreciate it if you could mark the helpful reply as Accepted Solution — it helps others who might face a similar issue.

Warm regards,

Prasanna Kumar

[v-pgoloju]

Hi @jmoedata,

Just following up to check if the solution shared by our Super User helped resolve your issue. If you're still facing difficulties or need further assistance, please let us know — we’re here to help!

If the response addressed your query, we kindly request you to mark it as Accepted Solution and click Yes if you found it helpful. This supports others in the community as well.

Best regards,

Prasanna Kumar

[v-pgoloju · Accepted solution]

Hi @jmoedata,

Thank you for reaching out to the Microsoft Fabric Forum Community.

And also, Thanks to @lbendlin  for prompt and helpful solution

Excel’s live connection to a Power BI semantic model (via PivotTable > From Power BI) creates a thin client connection that allows slicing and filtering after the data is loaded, but it doesn’t support parameter-driven filtering before retrieval. Power BI Service parameters are only used during dataset refresh and have no effect on what Excel pulls in. To limit data at query time, the best workaround is using DAX Studio or Power Query with the XMLA endpoint, where you can write custom DAX queries and even pass Excel cell values as parameters to filter data before it loads into Excel.

If you find this response helpful, please consider marking it as the accepted solution and giving it a thumbs-up to support others in the community.

Thank you & Regards,
Prasanna Kumar

[jmoedata]

Thank you for the responses.  Do you have any examples or tutorials that exist where Power Query with XMLA endpoints exist? Ideally, we want the users to enter data (or select it from a list) in Excel and pass those parameters to the semantic model. Thanks.

[lbendlin]

You can do that in a roundabout way - In Power Query you connect to the semantic model via the Analysis Services connector, select Import mode, and specify the DAX query.    

But why?  Why not directly run the DAX queries into an Excel table?

You cannot pass parameters from Excel to queries.  This needs to be done by the user manually.

[lbendlin]

Connnect your Excel file via the "live connection (500000 rows)"  (by the way, two lies for the price of one!) and teach your users how to modify the table query DAX.  Introduce them to DAX Studio Query Editor while you are at it.