Query running out of resources on "select all" but not when all selected!!!!

[mike_asplin]

Can some explain what the difference is?  If I have a client list and click select all or click rubber to select none the visual runs out of resources.

 However if I click all the clients individually so they are all selected it works 

The list of clients is being filtered by the Site slicer and also by a filter revenue>0 and date after 1/4/25.  I assume somethnig to do with the order in which the filters is being executed?  Is there anyway to solve this so "select all" works.  Seems odd behaviour

[cengizhanarslan]

I guess you might have null  Clients which you already filter in the slicer visual. If it is the case considering null values the filtered tables gets larger, thus returns an error.

[mike_asplin]

So you mean when I do select all firstly it gets all the cleints and after it filters for revenue >0 so much bigger table, than if I manually select the clients (who have been filtered for revnue >0 as list is a lot longer if i take that visual filter off)? Seems odd that select all isnt just selecting the clients that are listed. 

Is there anyway round this as getting this a lot where i think I am limiting the size of the table by filtering the visual, but seems that is almost just a visual reduction not an an actual calculation reduction. 

[johnt75 · Accepted solution]

If you do select all then it isn't filtering the main table by revenue > 0. That filter is applied to the slicer, not to the main table. Because select all actually doesn't pass any filter onto the main table, the filter for revenue > 0 is lost.

You could raise a bug report with MS about this, as it is likely not desired behaviour.

As a work around, you could create a measure like

Revenue > 0 =
IF ( CALCULATE ( [Revenue], ALLSELECTED ( 'Date' ) ) > 0, 1 )

and apply that as a Top N filter on the client in the main table. Set it to show only the Top 1 and I think that should restrict the visual to just those clients with revenue in the selected time period.

[mike_asplin]

That did improve things, but fell over as soon as I tried to expand any of the elements of the revenue, but actually that was falling over even when I selected the individual companies.

So you tip certianly helped reduce the number of clients bein fed into the calculation. Much appreciated as good tip as had thoguht by filtering the visual I was actually reducing the amount of data passing to the calculation. 

[mike_asplin]

I'll try that thanks 

[johnt75]

There's slightly different behaviour between using the "select all" and selecting each client individually. When you use "select all" that is the same as not applying any filter at all on the client, whereas selecting each individual client passes the list of clients as a filter.

You can also see the difference on the site slicer. When you select the clients individually only 2 sites are showing. When you use select all there are 4 sites showing. It is likely that it is these 2 additional sites which are somehow causing the query to exceed the available resources.

Without seeing the model its impossible to say exactly what is going on, but this might give you the base for further investigation.