Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills 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.

@ 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!:
Mastering Power BI 2nd Edition

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

PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

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

Top Solution Authors
Top Kudoed Authors