Issue with live connect to model in Excel - Data Driver does not include necessary capabilities

[tinadouglass51]

Hi, We use live connect in Excel to analysisservices model data from Power BI.  We have a user that is receiving this error when trying to select a filter option.

Data Driver does not include necessary capabilities and cannot be used with Excel.  Contact your database administrator or driver vendor.

Has anyone experienced this before and any suggestions?

Tina

[v-qiuyu-msft]

Hi @tinadouglass51, 

From your description, it looks like you are connecting to Power BI desktop model within the Excel file, right? 

Based on my test in Excel 2016 (16.0.4966.1000) MSO (16.0.4939.1000) 64 bit, connect to Power BI desktop model with localhost:<port> follow this document, it's able to create a PivotTable and set the filter. The Power BI desktop runs on my side is 2.79.5768.1082 64-bit (March 2020). Please run the same Power BI desktop version then test again. 

Best Regards,
Qiuyun Yu

[tinadouglass51]

Qiuyun

Hi, we are actually live connecting to a published model in Power BI (not desktop).  I'm not sure if that changes your feedback.  I can take a look at your link.

[v-qiuyu-msft]

Hi @tinadouglass51, 

I'm a little confused with your scenario. It seems you create a report based on a dataset in Power BI service directly, right? If it is, please go to dataset settings of this dataset, ensure both gateway connection (if required) and data source credential are set correctly. 

Best Regards,
Qiuyun Yu

[ebeery]

I've run into the same issue several times, and it's caused a lot of frustration because the issue seems transient with no clear fix or troubleshooting steps.   

In my case we're also using the "Analyze in Excel" feature (OLAP connection to a published Power BI dataset) and the issue seems specific to one particular filter field.  When attempting to change the filter, the error message is received.

The only "solution" I've found so far is to pull the problematic field into the "Rows" section of the PivotTable, and then immediately move it back to the "Filters" section.   After doing so, the filter works properly with no errors.  No idea why this "fix" works...

[DexterG_RxS]

@ebeery Thank you for this work around.  I can speak to the frustration first-hand, and the lack of support for this thread doesn't help.  

[dlaplante]

Did you ever get this resolved ? I'm having the same issue and the workaround provided by @ebeery is not working for me.

Update: OMG!!! I found the in the connection string, if you set the "Missing member mode" from "Error" to "Ignore", this work as expected !!! Hope this helps someone down the road!

[Anonymous]

@dlaplante Can you please clarify what did you mean in the Update comment?
I'm handling the same error.

Thanks in advance!

[DexterG_RxS]

@Anonymous Here is what @dlaplante was referring to.  I'm eager to try this out.  

I've also had some success with re-downloading & re-installing the "Analyze In Excel Updates".  The link for doing so is on the landing page for your Power BI server, about 4 icons to the left of your account picture/avatar, there is an arrow pointing down.