Skip to main content
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Resolver II
Resolver II

DirectQuery Result Exceed 1000000

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:







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?

Super User
Super User

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

Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

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.



Helpful resources

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.


Fabric Monthly Update - May 2024

Check out the May 2024 Fabric update to learn about new features.


Fabric certifications survey

Certification feedback opportunity for the community.