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.

[craigdormire]

Apologies for the SLOW response.  I guess I was hoping this system would somehow notify me if/when responses came in.  Maybe it got caught in the email spam/junk/filters 😞
Your suggestion looks promising, however, the output in your screenshot looks like a Table, which we already know how to filter/limit/control.  The issue we've had is in a Pivot Table connected to PBI Semantic Model.
Anyway, thanks for your responses/guidance.  We'll test this ASAP!

[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?

[craigdormire]

Technically, we are starting in Excel, and building a direct connection to our shared PBI semantic model, using the BUILD capabilities/permissions in PBI.  Yes, the model is published to our Premium/PROD workspace in the PBI service.  We use this Excel functionality for adhoc requests, to build quick pivot tables/charts with legacy approved Excel style formatting (often very difficult/impossible to replicate in PBI) for our non-PBI end users which often includes Sr. leaders used to seeing things a very specific way.  We also use CUBE formula functionality to bypass the TABLE/PIVOT TABLE functionality to build dynamic tables of data that can easily be refreshed as data changes/updates from month to month.  The issue really isn't a timeout, but a resource capacity limit (PBI Admin specific alerts) that gets triggered because of all of the micro-queries happening in the service, occurs during the data refresh, when we have a large number of formulas/queries to the model.  Basically, the PBI Premium Service is flagging our report/queries as exceeding 100% capacity, which means our entire PBI environment could be throttled, impacting other users in the company.  Obviously not something our PBI Admins want individual users to do.  If we can filter the model during the connection, instead of at the time of query/pivot/slicer refresh, I believe that will be much more efficient and minimize/eliminate the risk.