DirectQuery Result Exceed 1000000

[hnguyen76]

Hi All.

I have a very weird issue when using DirectQuery for Analysis Service. I have a disconnected table with only 4 rows, one each for each accounting period:

In a measure provided, I'm harvesting the user selected date and querying a base measure against the data coming from Analysis Services. The weird thing is, if I hardcode the period, it works perfectly fine, but the moment I try to add anything "dynamic" I receive the following error:

I believe the measure is simple enough:

Results as expected:

Same measure, but referenced a variable instead of hardcoding:

Result:

I tried using Performance Analyzer but since the query fails, I can't see what is being sent / processed by DirectQuery. Is there a way for me to check/understand what the issue may be?

[Greg_Deckler]

@hnguyen76 Do you really need KEEPFILTERS? Generally do not see it used like that.

[hnguyen76]

Hi @Greg_Deckler , 

I tend to use KEEPFILTERS more and more often nowadays mainly for performance benefits and to preserve any existing filters. Of course, by default the CALCULATE function overwrites any existing filter applied on the specified table/column.  

For example, I'm working with a live connected dataset with a large date table that holds data from 2016 up until 2050 (don't ask me why, lol).  This date table holds around 24 columns which is irrelevant to me 90% of the time. 

As a quick test, I just want to return the values for the last 4 days with this particular formula:

The result is as expected:

Using the performance analyzer this is my return:

A secondary test using KEEPFILTERS is as followed which nets me the same result:

But this time the query run is a bit faster:

And I believe this is due to the fact that instead of looking at the entire date table, it's only looking at one field and returning me the values for my criteria. This is just a simple exercise but I think it's even more relevant to use KEEPFILTERS when fields are coming from different dimensions then without it. Every second counts when you have executives looking at your dashboards expecting real-time results.