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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
SanjeevSoni
Frequent Visitor

DAX Query is slow when used in Cumulative over Date table

We are developing a Data Model for Project Control. One of the Query, when we use for one day, is works but quite slow, Below is the Snapshot from DAX Studio

SanjeevSoni_0-1600002503615.png

 

When We are using over Calendar; it is very slow and not usable. 

Below is the DAX Query: Each measure is referring to many Measures, 

 

%age Progress Actual:=DIVIDE (

CALCULATE (

SUMX (

VALUES( dMEDRDoc[Doc No] ),

[MH Actual Cutoff MEDR] * [%age Prog based on Return date] / 100

),

FILTER (

ALL ( ProjectCalendar ),

ProjectCalendar[Date] <= MAX ( ProjectCalendar[Date] )

)

),

[Total MH Based on MEDR]

)

Cheers!! Soni,  + 66 852434420

 

5 REPLIES 5
amitchandak
Super User
Super User

@SanjeevSoni , Check if there ate bi-direction joins. Try to 1 to Many single directional joins.

 

What are formula of these two

[MH Actual Cutoff MEDR] ,  [%age Prog based on Return date]

 

is there an opportunity to make this as a calculated column? -[MH Actual Cutoff MEDR] * [%age Prog based on Return date]

 

it will move part of the cost to data loading,

Thanks,  I shall prepae the data files and share .

Also I am exploring if I can move the some of the complex measure to Calculated Columns or get in mode using Power Query

Greg_Deckler
Super User
Super User

@SanjeevSoni - 

I have some DAX Performance Tuning articles here:

These are also good articles:

Sorry for the link spam but it's a broad topic and the information you have provided is limited. Couple specific comments. First, I don't like VALUES. Second, is there any way you can limit the use of ALL and use ALLEXCEPT potentially or REMOVEFILTERS or KEEPFILTERS? I question the use of a SUMX within a CALCULATE statement, that seems redundant/unnecessary but hard to say. 

 

Can you post sample source data and expected output?


@ 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

Thanks for taking your time to providing the Links and other suggesstions.
Earlier I was knowing only maqsoftware.com link all other refereces and infomraiton quite usefull. 

Let me review the measure and prepare the data to share with you.

 

Thanks once again

Anonymous
Not applicable

Define each measure as VAR (variable) then call those variables inside the actual measure .It will make your written dax fast.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors