Need to add a filter to the data connection in Excel to a Power BI semantic model?

[craigdormire]

I asked this question in the O365-Excel group 1st and was told to try the PBI group:

I have a large Power BI semantic model. In Excel, I know how to BUILD connected tables & pivots, but end users are hitting resource capacity limits during refresh and when using slicers to filter the data. I want to control the size of the data coming into Excel by filtering the data as far upstream in the data connection as possible.

For example, the model has categories/DIM tables, so if I could write a DAX or SQL query in the Excel connection that filters the data by Country (US or CAN only) or by date/year, that would shrink the dataset significantly, and allow the Excel report to more efficiently show only what is needed for specific requests without having to run the refresh or slicer queries against the full/large model.

I know I can manipulate the DAX code to filter an Excel table connection, so the functionality exists, but I don't see the same options when connecting to the model using an Excel Pivot Table.

I would like to add a (DAX or SQL) filter to the raw data connection string to shrink the data during the connection refresh, to minimize the data being queried, and then use Cube formulas or pivots in use cases where a flat table isn't the best option.

Here's a screenshot showing the Connection Properties in Excel & the functionality to filter using DAX (as shown) or SQL (in the dropbox).

Thanks for any ideas you may have!

[v-menakakota · Accepted solution]

Hi @craigdormire ,

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

You can modify the connection in Excel to include a DAX query.

Open Excel and go to Data > Get Data > From Power BI.  Connect to your Power BI dataset.
Once connected, go to Data > Queries & Connections.

Right-click on the connection, select Properties, and then go to the Definition tab.
Replace the default query with a filtered DAX query.

EVALUATE
 FILTER(
myexcel,
myexcel[Country]  IN  {"US", "CAN"} &&
myexcel[Year] = 2023
 )

Here is the screenshot for better understanding:

Excel only retrieves data for the specified filters (e.g., Country and Year).

If this post was helpful, please give us Kudos and consider marking Accept as solution to assist other members in finding it more easily.

Regards,
Menaka

[v-menakakota]

Hi @craigdormire ,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Regards,
Menaka.

[v-menakakota]

Hi @craigdormire ,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you.

[v-menakakota]

Hi @craigdormire ,

I hope this information is helpful. Please let me know if you have any further questions or if you'd like to discuss this further. If this answers your question, please Accept it as a solution and give it a 'Kudos' so others can find it easily.

Thank you.

[ToddChitt]

So let me get this straight. You have a Power BI model and are browsing that in Excel. Is that Power BI model hosted on the Power BI service? Power BI can handle models of hundreds of millions of rows. Where is the data timeout coming, when you try to "Analyze in Excel" from Power BI?