cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
apollo89
Helper II
Helper II

Filter drop down in Analyze in Excel

Hi All,

 

We use Analyze in Excel with PowerBI.

What we have noticed is that a filter drop down for any field in Analyze in Excel shows all of the data for the field contained in the OLAP dataset ignoring the current filter context in the pivot.

For example if a field has the data A,B,C in its OLAP dataset and if the other fields in the Analyze in Excel pivot have filtered it down to 'A', the filter drop down to this field would still show A,B,C.

Anything I am doing wrong?

 

Thanks! 

3 REPLIES 3
v-piga-msft
Resident Rockstar
Resident Rockstar

Hi @apollo89 ,

It seems that you have problems with Ananlyze in Excel.

I still have a little confused about your situation.

If it is convenient, could you share some screenshots show your sceanrio in more details so that I could have a test in my site?

Best Regards,

Cherry

 

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi Cherry,

 

Consider the following dataset:

 

Capture2.PNG

 

I entered this table in PowerBI desktop and published it to Power Bi Service.

The Analyze in Excel pivot then shows:

 

Capture.PNG 

 

Note 'VAL SUM' is a SUM Measure of 'VAL'

 

Let's say I want to see 'A' in ATTR1, then the pivot looks like:

 

Capture3.PNG

However now, if I filter on ATTR2, I see:

 

Capture4.PNG

 

Since ATTR1 is filtered on 'A', we should ideally be seeing only 'B' and 'P' in the ATTR2 filter drop down but we see all the values.

It looks like the all of the values is being fetched from the dataet since it is an OLAP data source. This is not the behaviour when the data is present locally in the Excel file itself.

 

Since our reports contain hundreds of thousands of values in such attributes, we face a severe performance issue while using the Analyze in Excel. Using the search box in the filter drop down also performs bad.

 

I have used slicer to show filtered data however since the slicer doesn't have a search box, it doesn;t work well for us.

Hope this makes this clear.

Thanks!

Hi @apollo89 ,

Thanks for your detailed explanation firstly.

By my tests, I could reproduce your issue in my site.

I have reported this issue internally and will update here if I get any information.

Sorry for any inconvenience.

Best  Regards,

Cherry

 

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
User Groups Public Preview

Join us for our User Group Public Preview!

Power BI User Groups are coming! Make sure you’re among the first to know when user groups go live for public preview.

March Update

Check it Out!

Click here to read more about the March 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors