cancel
Showing results for 
Search instead for 
Did you mean: 
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,

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin

Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!

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!!!
Check out my External Tool for Power BI Desktop! Microsoft Hates Greg's Quick Measures
YouTube Channel! Microsoft Hates Greg
Check out my latest book!

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
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.