Skip to main content
cancel
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.

Reply
hnguyen76
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:

hnguyen76_0-1619733771425.png


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:

hnguyen76_1-1619733911282.png

 

I believe the measure is simple enough:

hnguyen76_2-1619733976907.png

Results as expected:

hnguyen76_3-1619734121108.png

 

Same measure, but referenced a variable instead of hardcoding:

hnguyen76_4-1619734200131.png

 

Result:

hnguyen76_5-1619734242312.png

 

 

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?

2 REPLIES 2
Greg_Deckler
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. 

hnguyen76_0-1619747927388.png

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

hnguyen76_1-1619748359010.png


The result is as expected:

hnguyen76_3-1619748454223.png

 

Using the performance analyzer this is my return:

hnguyen76_4-1619748512779.png

 

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

hnguyen76_5-1619748552548.png

 

But this time the query run is a bit faster:

hnguyen76_6-1619748667200.png

 

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

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

MayPowerBICarousel

Fabric Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.